I'm writing myself an Access '97 address book and the ZIP Code formats are 

driving me nuts.  Even though the format is specified as 00000\-####, 

meaning it should be 12345 or 12345-6789, this what's happening.



ZIP Code       What I want.     What I get.

----------     ------------     -----------

00734          00734            734

07067          07067            7067

12345          12345            1-2345

12345-6789     12345-6789       12345-6789



For the form, I had to force the proper format by writing code for the "On 

Current" event.  Basically:



    If Me.ZIP_Code <= 99999 Then

        Me.ZIP_Code.Format = "00000"

    Else

        Me.ZIP_Code.Format = "00000-0000"

    End If



And that helped the form.  The report is another matter.  There is no "On 

Current".  Formatting in reports seems to be an all or nothing deal.  

Either they're all "00000" or all "00000-0000".  If I try to set the 

format property of ZIP Code in the report as "00000\-####" I get the mess 

I showed you above.



Any ideas on how to fix the report to show the proper formatting for each 

ZIP code even though each page can have records with various ZIP code 

formats?



Let's ignore foreign postal codes for this exercise, class.  :)



Thanks.

Greg

Hi Greg



Does changing the format to '!00000\-####' make any difference?



It should force the format to fill from left to right, rather than right 

to left.



-Brian



> I'm writing myself an Access '97 address book and the ZIP Code formats 

are 

> driving me nuts.  Even though the format is specified as 00000\-####, 

> meaning it should be 12345 or 12345-6789, this what's happening.

> 

> ZIP Code       What I want.     What I get.

> ----------     ------------     -----------

> 00734          00734            734

> 07067          07067            7067

> 12345          12345            1-2345

> 12345-6789     12345-6789       12345-6789

> 

> For the form, I had to force the proper format by writing code for 

the "On 

> Current" event.  Basically:

> 

>     If Me.ZIP_Code <= 99999 Then

>         Me.ZIP_Code.Format = "00000"

>     Else

>         Me.ZIP_Code.Format = "00000-0000"

>     End If

> 

> And that helped the form.  The report is another matter.  There is 

no "On 

> Current".  Formatting in reports seems to be an all or nothing deal.  

> Either they're all "00000" or all "00000-0000".  If I try to set the 

> format property of ZIP Code in the report as "00000\-####" I get the 

mess 

> I showed you above.

> 

> Any ideas on how to fix the report to show the proper formatting for 

each 

> ZIP code even though each page can have records with various ZIP code 

> formats?

> 

> Let's ignore foreign postal codes for this exercise, class.  :)

> 

> Thanks.

> Greg

How about changing the data type to text? That will stop Access from

changing your numbers. Besides, a zip code really is a text data type

since the numbers are merely placeholders and do not represent

mathematical numbers.



Richard Lobel

Accessible Data

richard@a... <mailto:richard@a...> 

Cell:  (xxx) xxx-xxxx

Fax:  (xxx) xxx-xxxx

Ditto, 



Never use a numeric data type for ZIP codes.



Here is another reason.  If you use a ZIP+4 format e.g.  12345-6789 the

computer will calculate the equivalent value or 5556.



Rand



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

From: Richard Lobel [mailto:richard@a...]

Sent: Wednesday, December 19, 2001 1:51 AM

To: Access

Subject: [access] RE: ZIP Code Zaniness



How about changing the data type to text? That will stop Access from

changing your numbers. Besides, a zip code really is a text data type

since the numbers are merely placeholders and do not represent

mathematical numbers.



Richard Lobel

Accessible Data

richard@a... <mailto:richard@a...>

Cell:  (xxx) xxx-xxxx

Fax:  (xxx) xxx-xxxx

Rand and others,



<< Never use a numeric data type for ZIP codes. >>



Thanks for the tip.  I converted the ZIP code field to text (which is 

useful with international postal codes anyway).  Problems fixed.



Greg