[kazooassistant]

My original post about a week ago asked how to set up a phone directory in Word. With all your help, I was able to produce a Word document that looked great. However, my supervisor wants the phone directory set up and printed using Access. I have not used Access very much, but was able to get the directory set up with a help from a colleague in another office.

The only problem we are having is the spacing between the last and first name.
My output looks like this: Jones (large space between) John
My supervisor wants it to look like this: Jones, John

How do I get the comma in between the names and do away with the large space???

Any help will be greatly, greatly, greatly appreciated.

[Don Sabo]

If you have two different fields, one for Last Name and one for First Name then you can use the following:

=Trim([Last Name] & " ," & [First Name])

You want to put a space before the comma within the quote marks.

[DrewAllen]

Quote:

Originally Posted by Don Sabo

If you have two different fields, one for Last Name and one for First Name then you can use the following:

=Trim([Last Name] & " ," & [First Name])

You want to put a space before the comma within the quote marks.

The closing paren should be after [Last Name]. If the last name is stored as "Smith_____ ", (I had to replace the spaces with underlines.) you're still going to get a lot of spaces between the last name and the comma. You want to Trim() the last name before adding the comma and first name, not Trim() the entire construction.

The space should be AFTER the comma, not before or you're going to get "Smith ,John" when what you want is "Smith, John".

Drew

[Melissa Siobhan]

Quote:

Originally Posted by DrewAllen

The closing paren should be after [Last Name]. If the last name is stored as "Smith_____ ", (I had to replace the spaces with underlines.) you're still going to get a lot of spaces between the last name and the comma. You want to Trim() the last name before adding the comma and first name, not Trim() the entire construction.Drew

I think that the space issue she is having is more due to the fact that she had a last name field that she made long enough to accommodate long names and then had a separate field for first name.

If she has spaces after her name I'd see your point but assuming she has clean data I usually trim the entire construction as in Don's example (with the exception of the comma error) and it works just fine that way.

[DrewAllen]

Quote:

Originally Posted by Melissa Siobhan

I think that the space issue she is having is more due to the fact that she had a last name field that she made long enough to accommodate long names and then had a separate field for first name.

If she has spaces after her name I'd see your point but assuming she has clean data I usually trim the entire construction as in Don's example (with the exception of the comma error) and it works just fine that way.

It actually depends on how the field is stored. I believe that in RE, it's a varchar field (variable length), so if it's linked in Access, you don't need to do a Trim() at all. However, if it's been imported into Access as a char field (fixed length), then it does require the trim of the Last Name.

Trim() removes spaces from the beginning and end of a field. It will not remove excess spaces from the middle of a field.

Drew

[Melissa Siobhan]

Drew,

You are probably right it probably is not needed but I saw it that way in an example one day and it worked and never caused problems so I kept using it. I learned much of access this way so I frequently use functions I really do not understand. (confession time!)

Thanks for your help.

[kazooassistant]

This forum is the best!!!!
Your help has solved my problem.
I've been a RE user for about 7 years and just joined this forum....this is such a great resource. Thanks!!

[Peter Gulka]

Quote:

Originally Posted by kazooassistant

This forum is the best!!!!

This is, of course, the correct answer