I use 4 address rows. Adress1, Address2 ,Town, Post code

Address2 is sometimes unused.

If I try to use address-line: =[address1]+", "+[addess2]+", "+[town]+", "+[postcode]+"."

then it is OK if all fields have something entered.

If address2 (say) is blank, I get nothing.

How can I get round this please?

regards

In your example, address2 probably contains Null instead of a blank so the concatination fails on an invalid reference. Try this:

address-line: =Nz([address1],"")&", "& Nz([addess2,"")&", "& Nz([town],"")& ", "& Nz([postcode],"") & "."

The Nz functions substitue a blank string if the values are Null, otherwise they use the field's value.

TomU

I have never heard of Nz before.

I am most obliged to you.

I will try the advice staright away.

Many thanks

Can I use the same function in a report?

regards

dfp

Welcome to Utter Access Forums ... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)
This is what you need instead of the Nz() function ...


In the above example .. if "address2" is Null .. the Null, comma, and space will be ignored that is within the parenthesis ...

RDH


Edited by: R. Hicks on Sun Jul 3 14:17:45 EDT 2005.

Ricky left out an & after the first , and the address2

should be:



Note: & and + do not worj the same. Normally you should use the &

Thanks Boyd ... I have atered my reply to reflect the change.

RDH

Keep up youe great work Ricky. I got you back! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/uarulez2.gif)

That's pretty cool! I had always used an IIF function to handle a possible null value in address2.

BTW, CONGRATULATIONS FOR YOUR RECOGNITION AS MVP!!!...JD

Thanks John .. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

This method can also be used when you concatenate mutiple lines in a txtbox such as:

ClientName
Address1
Address2
City, State
ZipCode

Using the method I posted .. it would eliminate an empty line should Address2 be a Null ...

RDH

How so? I was currious to see it work in a report but the best I could do with your method was three textboxes:

=[First] & " " & [Last]
=[Address1] & Chr(13) & Chr(10) & ([Address2]+"")
=[City] & ", " & [StateOrProvince] & " " & [PostalCode]

Whereas using IIF I can have one textbox with:

=[First] & " " & [Last] & Chr(13) & Chr(10) & [Address1] & Chr(13) & Chr(10) & IIf([Address2],[Address2] & Chr(13) & Chr(10),"") & [City] & ", " & [StateOrProvince] & " " & [PostalCode]

For the sake of my continued education, what did you have in mind?...JD

This should do what you need in a single txtbox ...

=[First] & " " & [Last] & Chr(13) & Chr(10) & [Address1] & Chr(13) & Chr(10) & ([Address2]+Chr(13)+Chr(10)) & [City] & ", " & [StateOrProvince] & " " & [PostalCode]

The above is a one single line expression ...
If Address2 is a Null value .. the line is skipped ...

RDH

Edited by: R. Hicks on Tue Jul 5 9:31:02 EDT 2005.

Excellent. When I tried it I used & for the CR and LF within Address2 and got an unrecognized square character within the address.

Thanks for showing me this new technique...JD

BTW, you left out an ampersand before [City].

You are welcome John .. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

As for the missing ampersand .. I did that to keep you on your toes ... LOL
I will edit my reply and fix it ..

RDH

Thank you all for your help with this , to you all perhaps, rather pathetic query.

I am now able to produce the concatenation that I wanted. ... many thanks.

On top of that the solution of enclosing round brackets around a list as described by R.Hicks has also solved a formatting problem that I gave up solving years ago. I just put up with the 'empty' line that occurred with an empty field.

Why have I never before thought of asking advice on a forum such as this?

Thank you all for taking the time to assist.

regards

Mr.Chips, Tonbridge, England

That is Great!!!
You are very Welcome for the help .. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

RDH

so.. following this thread, I came up with the following for addresses on a report:

=[Address Line 1] & ([Address Line 2]) & [City] & "," & [State/Province] & [Zip/Postal Code] & [Country]

The data is there but is lacking any line breaks. How do you do this so that after each field a line is created? Also, how do you avoid printing your own country i.e. skipping if = to....?

Carriage Return and Line Feed are:

Chr(13) & Chr(10)

so you need something like:

=[Address Line 1] & chr(13) & chr(10) & ([Address Line 2]) & [City] & "," & [State/Province] & " " & [Zip/Postal Code] & chr(13) & chr(10) & [Country]


NOTICE THAT I PUT TWO SPACES AFTER THE STATE/PROVINCE.

BTW, you probably shouldn't use slashes as part of a name for a field....JD

Here's a dumb one. Where do I stick that? I thought it was the control source, but apparantly not.

Have a look at the attached...JD