Formatting addresses

  • Thread starter Thread starter Patrick McGuire
  • Start date Start date
P

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
 
Patrick said:
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.
Click to expand...


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?
 
Thanks for the help
-----Original Message-----
Patrick said:
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.
Click to expand...


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?
Click to expand...
 
Back
Top