Concantonate Addresses

[bhammer]

My address list is a bit different in that it has only four
fields--StreetNumber, StreetName, Unit and Building. Up to three of these may
be blank (Null) for any given record in my Address table.

In my query I want to concantonate the address parts into one field. I
currently have:

Address: IIf(IsNull([StreetNumber]),"",[StreetNumber] & " ") &
IIf(IsNull([StreetName]),"",[StreetName] & " ") &
IIf(IsNull([Unit]),"","Unit " & [Unit] & " ") &
IIf(IsNull([Building]),"","Bldg. " & [Building])

This works, but rather than a space between each address part, I want a
comma then a space--but only if the preceeding part(s) is not Null. I want to
avoid, ", Unit 102, Bldg. 23" (with the leading comma and space in front with
nothing else preceeding it).

-Brad

 

[Marshall Barton]

My address list is a bit different in that it has only four
fields--StreetNumber, StreetName, Unit and Building. Up to three of these may
be blank (Null) for any given record in my Address table.

In my query I want to concantonate the address parts into one field. I
currently have:

Address: IIf(IsNull([StreetNumber]),"",[StreetNumber] & " ") &
IIf(IsNull([StreetName]),"",[StreetName] & " ") &
IIf(IsNull([Unit]),"","Unit " & [Unit] & " ") &
IIf(IsNull([Building]),"","Bldg. " & [Building])

This works, but rather than a space between each address part, I want a
comma then a space--but only if the preceeding part(s) is not Null. I want to
avoid, ", Unit 102, Bldg. 23" (with the leading comma and space in front with
nothing else preceeding it).

You can use +, which propogates Null, to concatenate the
parts you want to suppress:

Address: (StreetNumber + " ") & (StreetName)+ " ") &
("Unit " + Unit + " ") & ("Bldg. " + Building)

 

[bhammer]

Thanks, Marsh. Didn't know about the + trick.

But I still a way to test for null in the preceeding field, so that I can
not insert the comma, like this:

123 Elm St., Unit 102 'when the street address in NOT null.

Avoid this:
, Unit 102 'when the street address IS null.

-Brad

 

[bhammer]

Got it!

Address: [StreetNumber] & IIf(IsNull([StreetNumber]),[StreetName],("
"+[StreetName])) & IIf(IsNull([StreetNumber] & [StreetName]),("Unit
"+[Unit]),(", Unit "+[Unit])) & IIf(IsNull([StreetNumber] & [StreetName] &
[Unit]),("Bldg. "+[Building]),(", Bldg. "+[Building]))

 

[Marshall Barton]

Got it!

Address: [StreetNumber] & IIf(IsNull([StreetNumber]),[StreetName],("
"+[StreetName])) & IIf(IsNull([StreetNumber] & [StreetName]),("Unit
"+[Unit]),(", Unit "+[Unit])) & IIf(IsNull([StreetNumber] & [StreetName] &
[Unit]),("Bldg. "+[Building]),(", Bldg. "+[Building]))

I don't think IIf(IsNull([StreetNumber] & [StreetName]),...
does what you want. Maybe it would be better with:
IIf(IsNull([StreetNumber] + [StreetName]),...

But I am easily confused by all those IIfs. I would try
something more like:

Address: (StreetNumber + " ") & Mid((", " + StreetName) &
(", Unit " + Unit) & (", Bldg. " + Building), 3)

but maybe you can read a bunch of IIfs more easily.