Formatting addresses

[Patrick McGuire]

I have 5 fields: StreetAddress1, StreetAddress2, City,
State, PostalCode in my AccessXP database and want to put
these together in an address box on a report. I want it
to look good regardless of the presence or absence of
data in the respective fields. I have tried to use a
textbox whose ControlSource is set to the following:

=Nz([StreetAddress1] & (chr(10)+[StreetAddress2]) & chr
(10) & (([City]+", ") & [StateOrProvince]) & (" " +
[PostalCode]),"Not Available")

But when none of the fields contain data this results in
2 unprintable characters and a ", " (so it doesn't appear
to be propagating the nulls), and even when data are
present, it does not go to the next line when it should.

What am I doing wrong?

Thanks

Pat

 

[Marshall Barton]

I have 5 fields: StreetAddress1, StreetAddress2, City,
State, PostalCode in my AccessXP database and want to put
these together in an address box on a report. I want it
to look good regardless of the presence or absence of
data in the respective fields. I have tried to use a
textbox whose ControlSource is set to the following:

=Nz([StreetAddress1] & (chr(10)+[StreetAddress2]) & chr
(10) & (([City]+", ") & [StateOrProvince]) & (" " +
[PostalCode]),"Not Available")

But when none of the fields contain data this results in
2 unprintable characters and a ", " (so it doesn't appear
to be propagating the nulls), and even when data are
present, it does not go to the next line when it should.

Access requires you to use Chr(13) & Chr(10) in that order
to represent a new line. So the unprintable characters are
the Chr(10) that do not have an associated Chr(13).

I don't know why you're getting the comma, unless the City
field contains a zero length string instead of a Null (same
for the space and PostalCode). Just because you don't see a
value in the field doesn't mean it has to be Null, Check
the table fields, are they really Null? Do they have Allow
Zero Length set to Yes?

 

[Patrick McGuire]

Thanks for the help

-----Original Message-----

I have 5 fields: StreetAddress1, StreetAddress2, City,
State, PostalCode in my AccessXP database and want to put
these together in an address box on a report. I want it
to look good regardless of the presence or absence of
data in the respective fields. I have tried to use a
textbox whose ControlSource is set to the following:

=Nz([StreetAddress1] & (chr(10)+[StreetAddress2]) & chr
(10) & (([City]+", ") & [StateOrProvince]) & (" " +
[PostalCode]),"Not Available")

But when none of the fields contain data this results in
2 unprintable characters and a ", " (so it doesn't appear
to be propagating the nulls), and even when data are
present, it does not go to the next line when it should.

Access requires you to use Chr(13) & Chr(10) in that order
to represent a new line. So the unprintable characters are
the Chr(10) that do not have an associated Chr(13).

I don't know why you're getting the comma, unless the City
field contains a zero length string instead of a Null (same
for the space and PostalCode). Just because you don't see a
value in the field doesn't mean it has to be Null, Check
the table fields, are they really Null? Do they have Allow
Zero Length set to Yes?