Help with IIF Statement please. 2

[Rene1024]

Hello,

I'm trying to merge some fields to create a mailing address. I have the following fields:
AddrNum
Street
StreetDir
Unittype
UnitNumber

I would like to use the IIF statement on an update query to populate a new column called mailing address. In the Unitetype and Unitnumber columns I have some null values that I need to ignore when making the update.

Thanks for your help.

Rgds.

Rene

 

[PHV]

Take a look at the Nz function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rene1024]

Thanks for your reply PHV.

I'm not too familiar with the NZ function, I was hopping that I could do something like:

addrnum&" "&street&" "&streetdir&" "&unittype&" "&unitnumber

without bringing blank spaces when values were null.

Is this possible?

Thanks again for your help.

Rgds.

Rene

 

[Loktar]

You could try something like this:

Dim strAddress As String

strAddress = IIf(IsNull([addrnum]), vbNullString, [addrnum]) & _
             IIf(IsNull([street]), vbNullString, " " & [street])

and then keep going for the rest of your variables.

strAddress = vbNullString is basically the same thing as strAddress = "" but I've heard it runs faster in Access.

Hope some of that helps.

 

[Rene1024]

Thanks Loktar!