Long Numbers and Importing to Access

**Climber465** · 12-19-2006, 10:55 AM

Hello Everyone,

I basically have 2 questions.

1. I have a report that exports to a .csv spreadsheet. There are Postal Service tracking numbers and as I'm sure you all know they are sometimes LONG! Naturally Excel changes these to scientific representations. Well I have changed the format of the column holding these long numbers to a number format and it shows the long number in the cell. Unfortunately, it is still storing the scientific representation in the function line. When I double click on the cell to edit the long number contents, it changes to the scientific number again. Is there a way to convert the scientific number to the real number?

#2 & this is a GOOD ONE! I am importing this spreadsheet using a simple macro, it has worked for about a year and last month I started getting a "Numeric Overflow" error. I changed the field size in Access to ensure it would accommodate the longer numbers in the spread sheet and no luck. Here's the kicker, If I take the SAME information from the spread sheet and sort it by a different column, it will accept the import without an error. SAME information!

Does anyone know why the order in which the information is laid out would determine whether or not it would get the numeric overflow error??

Thanks!

~Richard Allen

P.S. I know very little VB coding.

**oldchippy** · 12-19-2006, 11:00 AM

Answer 1
Format numbers to text rather than numbers

Answer 2
http://support.microsoft.com/kb/827321

**Climber465** · 12-19-2006, 11:10 AM

Originally Posted by oldchippy

Answer 1
Format numbers to text rather than numbers

thanks for the quick response OldChippy.

Unfortunately I have already tried that with no luck. It still keeps the scientific number. The problem is if I have the field set to text before the long number is entered, it will keep it. But where there .csv file is automatically create, it seems to save it as the scientific and no matter what way I format the cell it doesn't change the scientific number. If you would like to give it a try, open excel and type in 4513485400000000, it will give you a scientific number, if you change to text, it keeps the scientific number, if you change to number it will show the whole number but the scientific is still stored in the formula line above...

I'll check out the website you sent for my 2nd questions.

Again, Thanks for the quick response!

**oldchippy** · 12-19-2006, 11:15 AM

If you format the cell first to text, then enter the number, the whole number will be displayed

**Climber465** · 12-19-2006, 11:22 AM

I understand that oldchippy,

The problem is that the excel spreadsheet is automatically generated with a general field format from a program that I have no control over. I was wondering if there was any way to convert the scientific number and show the whole number.

Also, sadly the web site didn't help me. It was about importing to Publisher, I need to import to Access. Although, it did give me some more details about the error message I'm getting.

Thanks!