Okay, I took you'll suggestions and concatenate using a query, but I am still getting empty spaces and blanks. I have been working on it all day and can't find what I am doing wrong. I have the attached the db with the query (qryFullAddress) in hopes someone can see what I missed. Any help is surely appreciated.

whats the problem?

whats the problem?

When a field is empty, I am getting blank line or empty space.

whats the problem?

I added the fields to "qryFullAddress with fields" so you can better see what I am talking about.

what do you want to achieve here?

what do you want to achieve here?

Eliminate the blank lines and empty spaces.

how about this one... i have made modification on the said query.

how about this one... i have made modification on the said query.

No, everything is join together. All I am trying to do is get my address to look properly on a form or if I decide to print them, and so. For example:

c/o ABC
ABC Tenant Inc.
123 Nowhere Lane
NY, New York 11234

Attn: CBS
CBS Production
222 Eastside N Avenue
Chicago, IL 12346

PO Box 1000
Houston, TX 77002

i see. let me test again.

Eliminate the blank lines and empty spaces.

Try this:

FullAddress: ([Attention]+(Chr(13) & Chr(10))) & ((IIf(IsNull([StreetNumber]),Null,CStr([streetnumber]))+" ") & ([PreDirection_Lkp]+" ") & ([StreetName]+" ") & [PostDirection_Lkp])+(Chr(13) & Chr(10)) & ([DesignationType]+" ") & ([Designation]+(Chr(13) & Chr(10))) & ([BoxType_Lkp]+" ") & ([BoxNumber]+(Chr(13) & Chr(10))) & ([City]+", ") & ([State]+" ") & ([ZipCode]+"-")+[ZipExt]

I found the numeric was causing a problem so I use the IIF to generate a variant. Presumably when seeing a number, the + operator tries to add not concatenate. This approach sorts out your extra spaces.

I also put brackets around all the Chr(13) & chr(10). If you consider [BoxNumber]+Chr(13) & Chr(10), then when box number is null, then the result will be only chr(10) since the operation [BoxNumber]+Chr(13) will be performed first - hence your extra line breaks.

As Bob Larson mentioned in another of your threads, you have kind of gone overboard with the normalisation. I too would put company name in the mane table and I'd take pretty much the same approach for all the other tables. It doesn't matter that you have blanks for no company name. This can always be managed in a query.

hth
Chris

Concatenation using + will preserve nulls, hence the blanks. Concatenation using & does not.

Try this:

FullAddress: ([Attention]+(Chr(13) & Chr(10))) & ((IIf(IsNull([StreetNumber]),Null,CStr([streetnumber]))+" ") & ([PreDirection_Lkp]+" ") & ([StreetName]+" ") & [PostDirection_Lkp])+(Chr(13) & Chr(10)) & ([DesignationType]+" ") & ([Designation]+(Chr(13) & Chr(10))) & ([BoxType_Lkp]+" ") & ([BoxNumber]+(Chr(13) & Chr(10))) & ([City]+", ") & ([State]+" ") & ([ZipCode]+"-")+[ZipExt]

I found the numeric was causing a problem so I use the IIF to generate a variant. Presumably when seeing a number, the + operator tries to add not concatenate. This approach sorts out your extra spaces.

I also put brackets around all the Chr(13) & chr(10). If you consider [BoxNumber]+Chr(13) & Chr(10), then when box number is null, then the result will be only chr(10) since the operation [BoxNumber]+Chr(13) will be performed first - hence your extra line breaks.

As Bob Larson mentioned in another of your threads, you have kind of gone overboard with the normalisation. I too would put company name in the mane table and I'd take pretty much the same approach for all the other tables. It doesn't matter that you have blanks for no company name. This can always be managed in a query.

hth
Chris

I don't understand the CompanyName part b/c when I asked this question a couple of months ago I was told to put CompanyName in another table. There are about 400 blind ads w/o a CompanyName. I use (N/A) when there is no CompanyName.

Concatenation using + will preserve nulls, hence the blanks. Concatenation using & does not.

Actually Chris, I think you got that backwards. If you use the + blanks will not appear whereas if you use the ampersand (&) blanks will remain.

Try this:

FullAddress: ([Attention]+(Chr(13) & Chr(10))) & ((IIf(IsNull([StreetNumber]),Null,CStr([streetnumber]))+" ") & ([PreDirection_Lkp]+" ") & ([StreetName]+" ") & [PostDirection_Lkp])+(Chr(13) & Chr(10)) & ([DesignationType]+" ") & ([Designation]+(Chr(13) & Chr(10))) & ([BoxType_Lkp]+" ") & ([BoxNumber]+(Chr(13) & Chr(10))) & ([City]+", ") & ([State]+" ") & ([ZipCode]+"-")+[ZipExt]

I found the numeric was causing a problem so I use the IIF to generate a variant. Presumably when seeing a number, the + operator tries to add not concatenate. This approach sorts out your extra spaces.

I also put brackets around all the Chr(13) & chr(10). If you consider [BoxNumber]+Chr(13) & Chr(10), then when box number is null, then the result will be only chr(10) since the operation [BoxNumber]+Chr(13) will be performed first - hence your extra line breaks.

As Bob Larson mentioned in another of your threads, you have kind of gone overboard with the normalisation. I too would put company name in the mane table and I'd take pretty much the same approach for all the other tables. It doesn't matter that you have blanks for no company name. This can always be managed in a query.

hth
Chris

Thanks a bunch for this code! I am embarrassed to say how long I have been working on it. Thanks again!

Actually Chris, I think you got that backwards. If you use the + blanks will not appear whereas if you use the ampersand (&) blanks will remain.Bob, I think you mean Neil ;)

Regards
Chris

Bob, I think you mean Neil ;)

Regards
Chris

Yep, my bad... sorry Chris :D :o

Stopher,

Any suggestions on how to handle CompanyNames? I don't want duplicate CompanyNames, but I have about 400 blind ads.

Bob, I think you mean Neil ;)

Regards
ChrisI think he meant Neil, and yes I got it backwards! ;)