If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Zip Code import from Excel
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" wrote in message ... 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" 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] |
#12
|
|||
|
|||
Zip Code import from Excel
Oops! Good point, Jeff. I am in the USA, and do get a bit USA-centric. I
will go sit in the corner, with my dunce cap on. "Jeff Boyce" wrote: 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" wrote in message ... 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" 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] |
#13
|
|||
|
|||
Zip Code import from Excel
No need ... I usually end up over-engineering (you should see the deck!).
Jeff "mnature" wrote in message ... Oops! Good point, Jeff. I am in the USA, and do get a bit USA-centric. I will go sit in the corner, with my dunce cap on. "Jeff Boyce" wrote: 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" wrote in message ... 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" 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] |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using other workbooks.. | DavidMunday | Worksheet Functions | 4 | July 1st, 2005 07:35 AM |
Make Change Case in Excel a format rather than formula | Kevin | Worksheet Functions | 1 | March 18th, 2005 08:53 PM |
SMTP Authentication fails | SharpEye | General Discussion | 0 | February 25th, 2005 08:49 AM |
Outlook XP email goes to sent folder -receipient doesn't receive,. | Jobde | General Discussion | 3 | February 9th, 2005 04:29 PM |
Change Needed - VB Code in excel | Eskimo | General Discussion | 2 | August 27th, 2004 11:40 PM |