|
|
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
|
|
bhammer wrote:
[Quoted Text] >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)
--
Marsh
MVP [MS Access]
|
|
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
|
|
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]))
|
|
bhammer wrote:
[Quoted Text] >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.
--
Marsh
MVP [MS Access]
|
|
|