Syntax error when changing field size using ALTER TABLE

[strict9]

Hello,

I am trying to use the following line to change a zip code field from
10 to 5 digits:

db.Execute "ALTER TABLE [Master] ALTER COLUMN [MailZip] NUMBER(5)"

However, it gives me the error "Sytax error in ALTER TABLE statement."
If I remove the (5) it works just fine. Any ideas?

 

[Brendan Reynolds]

You can't specify the number of characters for a numeric field, only for
text and binary fields.

Here's a link to the on-line help topic on the ALTER TABLE Statement ...

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP010322071033

 

[strict9]

Ah, and therein lies my confusion. Looks like just using TEXT(5) is
easier the. Thanks for the quick response!

 

[Brendan Reynolds]

Yes, a text field is generally the best choice for a field that will contain
something like a ZIP code or a phone number, e.g. it is made up of digits,
but you don't need to perform any kind of numeric operation on it. It will
also make life easier if you ever need to record non-US postal codes, which
include letters as well as digits.

Not sure about the five digits, though - I live in Ireland, so I'm no expert
on US ZIP codes, but I thought some of them were longer than that now?

 

[John Spencer]

Just FYI for Brendon.

US ZipCodes are 5 numeric characters; however, for increased definition you
can add 4 more numeric characters. The US Post Office would prefer that you
use Zip plus 4, but most people use just the first five. I believe that
large organizations tend to use the Zip plus 4 to get a discount on the
postage rate.

For instance, my ZIP is 21045-2603. Almost all my mail uses 21045.

John

 

[Brendan Reynolds]

Thanks John.

--
Brendan Reynolds

Just FYI for Brendon.

US ZipCodes are 5 numeric characters; however, for increased definition
you can add 4 more numeric characters. The US Post Office would prefer
that you use Zip plus 4, but most people use just the first five. I
believe that large organizations tend to use the Zip plus 4 to get a
discount on the postage rate.

For instance, my ZIP is 21045-2603. Almost all my mail uses 21045.

John