Re: Zip Code import from Excel

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Why would you want to prevent entry of any letters? A "digit-only" postal
code is very USA-centric. What about UK postal codes?

The main reason, in my mind, for not using a number data type is that
zip/postal codes ARE NOT NUMBERS! You don't add/subtract/multiply/divide
them ... OK, so you can, but you get meaningless answers. If they aren't
numbers, then they are characters ... text.

Just one person's opinion

Jeff Boyce
<Office/Access MVP>

"mnature" <mnature@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3758CFD8-1B92-4102-8598-80B21A474477@xxxxxxxxxxxxxxxx
> I, in turn, must disagree with John. It isn't that his method wouldn't
> work,
> but that there are sometimes several ways of getting to a similar place.
>
> If you want a "long" zip code, you can simply put 00000-0000 as the
> format.
> This will prevent you from putting any letters, as you should only be able
> to
> put numbers in. A text field will not prevent you from putting letters
> in.
> If you put this in as the format, you do not need to type the hyphen.
> Just
> put the numbers in, and the hyphen will place itself at the point you have
> indicated.
>
> Knowing two, or more, ways of getting to a solution can be very handy.
> Just
> use whichever one fits best to what you are doing.
>
> "John Vinson" wrote:
>
>> On Mon, 12 Dec 2005 14:36:06 -0800, "jjsun"
>> <jjsun@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>>
>> >I think we are close. I have created a table with the Field names ready
>> >and
>> >no records in the blank Access table. I formated the "zip" field as
>> >text. I
>> >then import (file, external database etc) the excel sheet but it gives
>> >me an
>> >error and says it cannot import file.
>>
>> The Format of the field IS IRRELEVANT. It only controls how the field
>> is displayed, not what's stored. I must disagree with mrnature's
>> proposed solution - for one thing it will prohibit ever entering Zip+4
>> codes such as 83660-6354.
>>
>> Instead use the Text *datatype*.
>>
>> To populate the table, use File... Get External Data... Link to *link*
>> to the spreadsheet (rather than importing it), and create an Append
>> query to append the data into the pre-built Access table. If this is
>> giving you an error, please post the error number and description,
>> along with an example of the type of data being imported.
>>
>> John W. Vinson[MVP]
>>


.

Relevant Pages

  • RE: How do I deal with the "subscript is out of range" message.
    ... The problem is that you have no control over the data coming from Excel. ... There is no way to be assured that it is in the same format as the previous ... I would suggest importing it into a separate table and then append to ...
    (microsoft.public.access.externaldata)
  • Re: Zip Code import from Excel
    ... The Format of the field IS IRRELEVANT. ... Instead use the Text *datatype*. ... to the spreadsheet (rather than importing it), ... query to append the data into the pre-built Access table. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Importing Date Problem
    ... set my table I'm importing to "short date" format. ... I'd suggest *linking* to the text file (rather than importing); run an Append ... Use a calculated field ...
    (microsoft.public.access.gettingstarted)
  • Re: Zip Code import from Excel
    ... Good point, Jeff. ... A text field will not prevent you from putting letters ... >> If you put this in as the format, you do not need to type the hyphen. ... >>> to the spreadsheet (rather than importing it), ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Im hoping This is the Right Place
    ... I will have to go take another look at how things are set up on the systems that use the Cactus program (there are 7 and they all send these letters and they all are sending them as attachments now) and see if I can find the setting for them to send them embedded rather than attached. ... the format ... The 'technicians I spoke to' does refer to Cactus Techs and they are telling me that their program - Cactus - does not control the way the letters are merged into Outlook. ...
    (microsoft.public.word.mailmerge.fields)