P.O. Box and concatenation hiccup

[Guest]

So far, I have successfully concatenated a full address in a parameter query,
the code is below. I have a report based on the query. The concatenation is
successful but for the situation of a P.O. Box; I need the text "P.O. Box"
to appear when there is a P.O.Box involved.

I also plan to try and do the same concatenation in VBA behind the report,
which I may have to do to solve my problem(using and IIf Else statement?).

Advice on how to fix this in the query and/or in VBA would be great. I
would like to get this db available to use soon.

FullAddress: [Org] & IIf(IsNull([Building]),"",[Building] & Chr(13) &
Chr(10)) & IIf(IsNull([Room]),"",[Room] & Chr(13) & Chr(10)) &
IIf(IsNull([Street]),"",[Room] & Chr(13) & Chr(10)) &
IIf(IsNull([POBox]),"",[POBox] & Chr(13) & Chr(10)) & IIf(IsNull([Street]),"
",[Street] & Chr(13) & Chr(10)) & IIf(IsNull([City])," ",[City] & ",") &
IIf(IsNull([Province])," ",[Province] & Chr(13) & Chr(10)) &
IIf(IsNull([PostalCode])," ",[PostalCode] & Chr(13) & Chr(10))

An example of the successful concatenation is:

Animal Health Centre
1767 Angus Campbell Rd
Abbotsford,BC
V3G 2M3

An example of the problem is:

Laboratoire d'épidémoisurveillance animale du Quebec
3500 <-- P.O. Box required in front
3220 Rue Sicotte
St. Hyacinthe,Quebec
J2S 7X9

Thanks,
AG

 

[Jeff Boyce]

I couldn't tell from your description if your [PO Box] field holds only the
"number", or holds the text and number. I'll assume the former.

If you want to have "PO Box" & [PO Box], just do that (concatenate the
string "PO Box" before the field).

Or am I missing something?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Stefan Hoffmann]

hi,

So far, I have successfully concatenated a full address in a parameter query,
the code is below. I have a report based on the query. The concatenation is
successful but for the situation of a P.O. Box; I need the text "P.O. Box"
to appear when there is a P.O.Box involved.

See OH for '&' and '+':

String & NULL = String
String + NULL = NULL

IIf(IsNull([POBox]),"",[POBox] & Chr(13) & Chr(10)) &

translates to

("P.O. Box " + [POBox] + vbCrLf) &

In full it should be:

FullAddress:
[Org] &
([Building] + vbCrLf) &
([Room] + vbCrLf) &
([Street] + vbCrLf) &
("P.O. Box " + [POBox] + vbCrLf) &
...


mfG
--> stefan <--