Formatted 9 digit zip

[Guest]

I am using Access 2002 and the Label Wizard. I use a query as input and the
table with the data has the input mask set for the 9 digit zip. The query
output is formatted correctly 99999-9999, but he label report is not
formatted. I tried it in 2000 and it works fine. Any suggestions? I set the
input mask in the table and the query (even though I shouldn't have to do the
query).

 

[Wayne Morgan]

Open the report in design view and check the Format property (Format tab)
and Input Mask property (Data tab) of the textbox that displays the zip
code. What does each say?

 

[Guest]

The text box has the city state and zip in it for the label. I went ahead and
put the input mask too just to try it - no difference. If the zip were in a
text box by itself it would work.

 

[fredg]

I am using Access 2002 and the Label Wizard. I use a query as input and the
table with the data has the input mask set for the 9 digit zip. The query
output is formatted correctly 99999-9999, but he label report is not
formatted. I tried it in 2000 and it works fine. Any suggestions? I set the
input mask in the table and the query (even though I shouldn't have to do the
query).

This is almost the exact same message as posted a few hours earlier.
Here is the same reply:

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.

If this is a classroom project, you guys really should ask your
instructor. That's what he gets paid for!

 

[Guest]

I saw that and I typed that in exactly as you have it written and it did not
work - is it case sensitive?

I am using Access 2002 and the Label Wizard. I use a query as input and the
table with the data has the input mask set for the 9 digit zip. The query
output is formatted correctly 99999-9999, but he label report is not
formatted. I tried it in 2000 and it works fine. Any suggestions? I set the
input mask in the table and the query (even though I shouldn't have to do the
query).

This is almost the exact same message as posted a few hours earlier.
Here is the same reply:

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.

If this is a classroom project, you guys really should ask your
instructor. That's what he gets paid for!

 

[Douglas J. Steele]

"did not work" doesn't tell anyone very much.

What happened when you tried it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I saw that and I typed that in exactly as you have it written and it did
not
work - is it case sensitive?

I am using Access 2002 and the Label Wizard. I use a query as input and
the
table with the data has the input mask set for the 9 digit zip. The
query
output is formatted correctly 99999-9999, but he label report is not
formatted. I tried it in 2000 and it works fine. Any suggestions? I set
the
input mask in the table and the query (even though I shouldn't have to
do the
query).

This is almost the exact same message as posted a few hours earlier.
Here is the same reply:

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.

If this is a classroom project, you guys really should ask your
instructor. That's what he gets paid for!

 

[Guest]

Nothing, same result, no formatting on zip code, still 999999999 with no dash

"did not work" doesn't tell anyone very much.

What happened when you tried it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I saw that and I typed that in exactly as you have it written and it did
not
work - is it case sensitive?

On Mon, 10 Oct 2005 14:03:05 -0700, IrishAnne wrote:

I am using Access 2002 and the Label Wizard. I use a query as input and
the
table with the data has the input mask set for the 9 digit zip. The
query
output is formatted correctly 99999-9999, but he label report is not
formatted. I tried it in 2000 and it works fine. Any suggestions? I set
the
input mask in the table and the query (even though I shouldn't have to
do the
query).

This is almost the exact same message as posted a few hours earlier.
Here is the same reply:

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.

If this is a classroom project, you guys really should ask your
instructor. That's what he gets paid for!

 

[Douglas J. Steele]

Exactly what did you type as the control source for the control?

What Fred gave you should definitely work with no additional fussing
required. And no, it's not case sensitive.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Nothing, same result, no formatting on zip code, still 999999999 with no
dash

"did not work" doesn't tell anyone very much.

What happened when you tried it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I saw that and I typed that in exactly as you have it written and it did
not
work - is it case sensitive?

:

On Mon, 10 Oct 2005 14:03:05 -0700, IrishAnne wrote:

I am using Access 2002 and the Label Wizard. I use a query as input
and
the
table with the data has the input mask set for the 9 digit zip. The
query
output is formatted correctly 99999-9999, but he label report is not
formatted. I tried it in 2000 and it works fine. Any suggestions? I
set
the
input mask in the table and the query (even though I shouldn't have
to
do the
query).

This is almost the exact same message as posted a few hours earlier.
Here is the same reply:

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.

If this is a classroom project, you guys really should ask your
instructor. That's what he gets paid for!

 

[Guest]

=([City] & ", " & [State] & " " & iif(len(zip])>6,left([zip,5) & "-" &
right([zip],4),[Zip])

Exactly what did you type as the control source for the control?

What Fred gave you should definitely work with no additional fussing
required. And no, it's not case sensitive.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Nothing, same result, no formatting on zip code, still 999999999 with no
dash

"did not work" doesn't tell anyone very much.

What happened when you tried it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I saw that and I typed that in exactly as you have it written and it did
not
work - is it case sensitive?

:

On Mon, 10 Oct 2005 14:03:05 -0700, IrishAnne wrote:

I am using Access 2002 and the Label Wizard. I use a query as input
and
the
table with the data has the input mask set for the 9 digit zip. The
query
output is formatted correctly 99999-9999, but he label report is not
formatted. I tried it in 2000 and it works fine. Any suggestions? I
set
the
input mask in the table and the query (even though I shouldn't have
to
do the
query).

This is almost the exact same message as posted a few hours earlier.
Here is the same reply:

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.

If this is a classroom project, you guys really should ask your
instructor. That's what he gets paid for!

 

[Guest]

It doesn't make sense that you copied that from the control source of the
text box.
Try this

=[City] & ", " & [State] & " " & iif(len([zip])>6,left([zip],5) & "-" &
right([zip],4),[Zip])

--
I hope that helped
Good luck

=([City] & ", " & [State] & " " & iif(len(zip])>6,left([zip,5) & "-" &
right([zip],4),[Zip])

Exactly what did you type as the control source for the control?

What Fred gave you should definitely work with no additional fussing
required. And no, it's not case sensitive.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Nothing, same result, no formatting on zip code, still 999999999 with no
dash

:

"did not work" doesn't tell anyone very much.

What happened when you tried it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I saw that and I typed that in exactly as you have it written and it did
not
work - is it case sensitive?

:

On Mon, 10 Oct 2005 14:03:05 -0700, IrishAnne wrote:

I am using Access 2002 and the Label Wizard. I use a query as input
and
the
table with the data has the input mask set for the 9 digit zip. The
query
output is formatted correctly 99999-9999, but he label report is not
formatted. I tried it in 2000 and it works fine. Any suggestions? I
set
the
input mask in the table and the query (even though I shouldn't have
to
do the
query).

This is almost the exact same message as posted a few hours earlier.
Here is the same reply:

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.

If this is a classroom project, you guys really should ask your
instructor. That's what he gets paid for!

 

[Wayne Morgan]

If you have concatenated multiple fields in the textbox's Control Source,
you'll need to use a Format statement in the Control Source.

Example:
=[City] & ", " & [State] & " " & Format([Zip], "00000-####")

 

[fredg]

=([City] & ", " & [State] & " " & iif(len(zip])>6,left([zip,5) & "-" &
right([zip],4),[Zip])

Exactly what did you type as the control source for the control?

What Fred gave you should definitely work with no additional fussing
required. And no, it's not case sensitive.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Nothing, same result, no formatting on zip code, still 999999999 with no
dash

:

"did not work" doesn't tell anyone very much.

What happened when you tried it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I saw that and I typed that in exactly as you have it written and it did
not
work - is it case sensitive?

:

On Mon, 10 Oct 2005 14:03:05 -0700, IrishAnne wrote:

I am using Access 2002 and the Label Wizard. I use a query as input
and
the
table with the data has the input mask set for the 9 digit zip. The
query
output is formatted correctly 99999-9999, but he label report is not
formatted. I tried it in 2000 and it works fine. Any suggestions? I
set
the
input mask in the table and the query (even though I shouldn't have
to
do the
query).

This is almost the exact same message as posted a few hours earlier.
Here is the same reply:

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.

If this is a classroom project, you guys really should ask your
instructor. That's what he gets paid for!

Your expression, as written, will give an error and would not be
accepted by Access as you have one too many opening parenthesis (your
mistake) and are missing some brackets (my mistake),
The first parenthesis is in error.
=([City] & ", " & [State] etc.. should be:
=[City] & ", " & [State] etc.

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

Try the entire expression this way:

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

 

[BruceM]

You have an extra parentheses after the = sign. Also, the code assumes that
ZIP is the name of the zip code field. If you are using a different name
for the field, substitute that. One more thing: note how Zip is enclosed
in square brackets in the Right function. It needs to be likewise enclosed
in square brackets in the Left function:
=[City] & ", " & [State] & " " & Iif(len([Zip])>6,Left([Zip],5) & "-" &
Right([zip],4),[Zip])

=([City] & ", " & [State] & " " & iif(len(zip])>6,left([zip,5) & "-" &
right([zip],4),[Zip])

Exactly what did you type as the control source for the control?

What Fred gave you should definitely work with no additional fussing
required. And no, it's not case sensitive.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Nothing, same result, no formatting on zip code, still 999999999 with
no
dash

:

"did not work" doesn't tell anyone very much.

What happened when you tried it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I saw that and I typed that in exactly as you have it written and it
did
not
work - is it case sensitive?

:

On Mon, 10 Oct 2005 14:03:05 -0700, IrishAnne wrote:

I am using Access 2002 and the Label Wizard. I use a query as
input
and
the
table with the data has the input mask set for the 9 digit zip.
The
query
output is formatted correctly 99999-9999, but he label report is
not
formatted. I tried it in 2000 and it works fine. Any suggestions?
I
set
the
input mask in the table and the query (even though I shouldn't
have
to
do the
query).

This is almost the exact same message as posted a few hours
earlier.
Here is the same reply:

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.

If this is a classroom project, you guys really should ask your
instructor. That's what he gets paid for!

 

[Guest]

Worked like a charm. I thought that I needed brackets around the field names,
but I wouldn't have caught the extra paren at the beginning.

Thanks a bunch.

You have an extra parentheses after the = sign. Also, the code assumes that
ZIP is the name of the zip code field. If you are using a different name
for the field, substitute that. One more thing: note how Zip is enclosed
in square brackets in the Right function. It needs to be likewise enclosed
in square brackets in the Left function:
=[City] & ", " & [State] & " " & Iif(len([Zip])>6,Left([Zip],5) & "-" &
Right([zip],4),[Zip])

=([City] & ", " & [State] & " " & iif(len(zip])>6,left([zip,5) & "-" &
right([zip],4),[Zip])

Exactly what did you type as the control source for the control?

What Fred gave you should definitely work with no additional fussing
required. And no, it's not case sensitive.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Nothing, same result, no formatting on zip code, still 999999999 with
no
dash

:

"did not work" doesn't tell anyone very much.

What happened when you tried it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I saw that and I typed that in exactly as you have it written and it
did
not
work - is it case sensitive?

:

On Mon, 10 Oct 2005 14:03:05 -0700, IrishAnne wrote:

I am using Access 2002 and the Label Wizard. I use a query as
input
and
the
table with the data has the input mask set for the 9 digit zip.
The
query
output is formatted correctly 99999-9999, but he label report is
not
formatted. I tried it in 2000 and it works fine. Any suggestions?
I
set
the
input mask in the table and the query (even though I shouldn't
have
to
do the
query).

This is almost the exact same message as posted a few hours
earlier.
Here is the same reply:

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.

If this is a classroom project, you guys really should ask your
instructor. That's what he gets paid for!

 

[BruceM]

Glad to hear it worked. To give credit where it's due, I noticed later that
Fred's answer provided the same information. At the time I tried to post my
answer we were having some network issues here, so there was a long delay
before my message was posted or new messages arrived.

Worked like a charm. I thought that I needed brackets around the field
names,
but I wouldn't have caught the extra paren at the beginning.

Thanks a bunch.

You have an extra parentheses after the = sign. Also, the code assumes
that
ZIP is the name of the zip code field. If you are using a different name
for the field, substitute that. One more thing: note how Zip is
enclosed
in square brackets in the Right function. It needs to be likewise
enclosed
in square brackets in the Left function:
=[City] & ", " & [State] & " " & Iif(len([Zip])>6,Left([Zip],5) & "-" &
Right([zip],4),[Zip])

=([City] & ", " & [State] & " " & iif(len(zip])>6,left([zip,5) & "-" &
right([zip],4),[Zip])

:

Exactly what did you type as the control source for the control?

What Fred gave you should definitely work with no additional fussing
required. And no, it's not case sensitive.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Nothing, same result, no formatting on zip code, still 999999999
with
no
dash

:

"did not work" doesn't tell anyone very much.

What happened when you tried it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I saw that and I typed that in exactly as you have it written and
it
did
not
work - is it case sensitive?

:

On Mon, 10 Oct 2005 14:03:05 -0700, IrishAnne wrote:

I am using Access 2002 and the Label Wizard. I use a query as
input
and
the
table with the data has the input mask set for the 9 digit
zip.
The
query
output is formatted correctly 99999-9999, but he label report
is
not
formatted. I tried it in 2000 and it works fine. Any
suggestions?
I
set
the
input mask in the table and the query (even though I shouldn't
have
to
do the
query).

This is almost the exact same message as posted a few hours
earlier.
Here is the same reply:

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.

If this is a classroom project, you guys really should ask your
instructor. That's what he gets paid for!