Formatting a Zip Code

[Carla]

I have Access 2000. I made a mailing list database and for the life of
me can't remember how to format the zip code field so it will print on
the label as xxxxx-xxxx. I have tried various - 9, #, 0 - options but
the "-" is not printing. It is running the numbers together.

What am I doing wrong?

I am using the label wizard and have the table set with zip code as a
separate field.

Thanks

[William Hindman]

00000\-9999;0;
hth

William Hindman

"Carla" <br******@gilanet.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

>I have Access 2000. I made a mailing list database and for the life of
me can't remember how to format the zip code field so it will print on
the label as xxxxx-xxxx. I have tried various - 9, #, 0 - options but
the "-" is not printing. It is running the numbers together.

What am I doing wrong?

I am using the label wizard and have the table set with zip code as a
separate field.

Thanks

[fredg]

On Mon, 2 Oct 2006 14:26:55 -0400, William Hindman wrote:

00000\-9999;0;
hth

William Hindman

"Carla" <br******@gilanet.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

>>I have Access 2000. I made a mailing list database and for the life of
me can't remember how to format the zip code field so it will print on
the label as xxxxx-xxxx. I have tried various - 9, #, 0 - options but
the "-" is not printing. It is running the numbers together.

What am I doing wrong?

I am using the label wizard and have the table set with zip code as a
separate field.

Thanks

If ALL the records are in 9 digit value, using an unbound control:
=Format([ZIP],"00000-0000")

If you may have some 5 and 9 digit values, then use:
=IIf(Len([ZIP])>6,Left([ZIP],5) & "-" & Right([ZIP,4),Left([ZIP],5))

The above will work if the ZIP values are like 123456789 or 12345-6789
or 12345-.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Bas Cost Budde]

The "-" must be a literal character in your format. I'm not sure about
it, because I seldom format my output :) but you should get any
character by prefixing it with a backslash.

So if your zip is a numerical field of 9 digits, "00000\-0000" will
return your format.

Carla schreef:

I have Access 2000. I made a mailing list database and for the life of
me can't remember how to format the zip code field so it will print on
the label as xxxxx-xxxx. I have tried various - 9, #, 0 - options but
the "-" is not printing. It is running the numbers together.

What am I doing wrong?

I am using the label wizard and have the table set with zip code as a
separate field.

Thanks

--
Bas Cost Budde
Holland
www.heuveltop.nl/BasCB/msac_index.html

[Carla]

Ok, in the design mode of the data table on the FORMAT line for the
PostalCode field, I took out the formatting. Trying to put anything in
there was making it worse - even the raw database was now joining the
numbers together if I tried to put 00000\-9999;0; in there or one of
the other suggestions.

Now, in a regular tabular report that I had previously made, my zip
code shows correctly. 99999- if there are no 4 digit extras, and
99999-0000 if there are.

My labels, tho, are now one label per line instead of 3 across, and the
numbers are still joined together.

In the design of the label it looks like this:

=Trim ([FirstName] & " " & [LastName])
=Trim([Address])
=Trim([City] & "" [State] & "" & [PostalCode]

The zip code in this label report is not showing a hyphen. I even
redid it to be sure. The zip code is run together with no hyphen. :(

To get the label report to show the zip code properly, where do I put
the formatting commands?

[fredg]

On 3 Oct 2006 08:47:51 -0700, Carla wrote:

Ok, in the design mode of the data table on the FORMAT line for the
PostalCode field, I took out the formatting. Trying to put anything in
there was making it worse - even the raw database was now joining the
numbers together if I tried to put 00000\-9999;0; in there or one of
the other suggestions.

Now, in a regular tabular report that I had previously made, my zip
code shows correctly. 99999- if there are no 4 digit extras, and
99999-0000 if there are.

My labels, tho, are now one label per line instead of 3 across, and the
numbers are still joined together.

In the design of the label it looks like this:

=Trim ([FirstName] & " " & [LastName])
=Trim([Address])
=Trim([City] & "" [State] & "" & [PostalCode]

The zip code in this label report is not showing a hyphen. I even
redid it to be sure. The zip code is run together with no hyphen. :(

To get the label report to show the zip code properly, where do I put
the formatting commands?

Always include the relevant part of any previous post when replying.
It makes it easier for someone to reply, as the previous message is
right in front of them.

There is no need to format the data in the table, as no one should be
looking at the table anyway.

In the report .......
Leave the Format PROPERTY of the control blank.
Use an UNBOUND control.
Set it's Control Source to:

=[City] & ", " [State] & " " &
IIf(Len([PostalCode])>6,Left([PostalCode],5) & "-" &
Right([PostalCode,4),Left([PostalCode],5))

** Note: I've added a comma and space after the City and a Space after
the State i.e. Omaha, NE 12345-6985

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail