Access 2000 not printing 9 digit zip codes correctly

[cawesq]

Hello,

I am using an Access 2000 database for a mailing list. Using the Label
Wizard, I created a report for printing mailing labels. However, when it
prints, it does not put the dash in between the fifth and sixth digit. So, my
zip code on the input form:

21144-1669

looks like this on the label:

211441669

In the report for the label, it is using the &[PostalCode] construct, which
seems to be the default for the zip code field.

What am I missing that the zip codes don't print with the dash? Any thoughts
or ideas are most welcome. Thanks for your help!

Chris Wilcox

 

[fredg]

Hello,

I am using an Access 2000 database for a mailing list. Using the Label
Wizard, I created a report for printing mailing labels. However, when it
prints, it does not put the dash in between the fifth and sixth digit. So, my
zip code on the input form:

21144-1669

looks like this on the label:

211441669

In the report for the label, it is using the &[PostalCode] construct, which
seems to be the default for the zip code field.

What am I missing that the zip codes don't print with the dash? Any thoughts
or ideas are most welcome. Thanks for your help!

Chris Wilcox

Your Zip code field is not STORING the hyphen with the data, even if
the table field shows the hyphen. You probably entered the data using
an Input Mask which was not set up to store the mask (00000\-9999;;_),
so instead of storing what you see (12345-4569) the data is actually
stored as 123454569.

In the label report change the control's control source from:
=[City] & ", " & [State] & " " & [Zip]
to:
=[City] & ", " & [State] & " " & Iif(Len(Zip])>6,Left([ZIP,5) & "-" &
Right([ZIP],4),[ZIP])

The above will work for 5 or 9 digit Zip codes.

An alternative is to run an Update query to convert all the stored
data to include the hyphen.
Then change the Input Mask to be saved with the data.
It should read:
00000\-9999;0;_
Future entries will include the hyphen.

 

[fredg]

Hello,

I am using an Access 2000 database for a mailing list. Using the Label
Wizard, I created a report for printing mailing labels. However, when it
prints, it does not put the dash in between the fifth and sixth digit. So, my
zip code on the input form:

21144-1669

looks like this on the label:

211441669

In the report for the label, it is using the &[PostalCode] construct, which
seems to be the default for the zip code field.

What am I missing that the zip codes don't print with the dash? Any thoughts
or ideas are most welcome. Thanks for your help!

Chris Wilcox

Your Zip code field is not STORING the hyphen with the data, even if
the table field shows the hyphen. You probably entered the data using
an Input Mask which was not set up to store the mask (00000\-9999;;_),
so instead of storing what you see (12345-4569) the data is actually
stored as 123454569.

In the label report change the control's control source from:
=[City] & ", " & [State] & " " & [Zip]
to:
=[City] & ", " & [State] & " " & Iif(Len(Zip])>6,Left([ZIP,5) & "-" &
Right([ZIP],4),[ZIP])

The above will work for 5 or 9 digit Zip codes.

An alternative is to run an Update query to convert all the stored
data to include the hyphen.
Then change the Input Mask to be saved with the data.
It should read:
00000\-9999;0;_
Future entries will include the hyphen.

I notice that I inadvertently left off some brackets when I sent my
reply.

Try the entire expression this way:

=[City] & ", " & [State] & " " & Iif(Len([Zip])>6,Left([ZIP],5) & "-"
& Right([ZIP],4),[ZIP])

 

[cawesq]

Hi Fred,

Thanks, it worked like a charm. You da Man!

Chris

Hello,

I am using an Access 2000 database for a mailing list. Using the Label
Wizard, I created a report for printing mailing labels. However, when it
prints, it does not put the dash in between the fifth and sixth digit. So, my
zip code on the input form:

21144-1669

looks like this on the label:

211441669

In the report for the label, it is using the &[PostalCode] construct, which
seems to be the default for the zip code field.

What am I missing that the zip codes don't print with the dash? Any thoughts
or ideas are most welcome. Thanks for your help!

Chris Wilcox

Your Zip code field is not STORING the hyphen with the data, even if
the table field shows the hyphen. You probably entered the data using
an Input Mask which was not set up to store the mask (00000\-9999;;_),
so instead of storing what you see (12345-4569) the data is actually
stored as 123454569.

In the label report change the control's control source from:
=[City] & ", " & [State] & " " & [Zip]
to:
=[City] & ", " & [State] & " " & Iif(Len(Zip])>6,Left([ZIP,5) & "-" &
Right([ZIP],4),[ZIP])

The above will work for 5 or 9 digit Zip codes.

An alternative is to run an Update query to convert all the stored
data to include the hyphen.
Then change the Input Mask to be saved with the data.
It should read:
00000\-9999;0;_
Future entries will include the hyphen.

I notice that I inadvertently left off some brackets when I sent my
reply.

Try the entire expression this way:

=[City] & ", " & [State] & " " & Iif(Len([Zip])>6,Left([ZIP],5) & "-"
& Right([ZIP],4),[ZIP])