Run Time Error 3349 Numeric Overflow

[strangeryet]

I am getting an error when I try to import an excel file;
However I did not get this error prior to splitting the database. There is an Account number field in the excel file that will contain an account number or all nines (99999999). When I delete the records that have nines (the ones that are aligned to the left in the cell), the import works. But why did the import work prior to the split without removing these records?
I did a search on the web and found a few areas where other people have experienced the same thing, but there were no resolutions. Perhaps someone on this site can help?

Thanks

 

[SkipVought]

Hi,

Is the AccountNumber FIELD a NUMBER or TEXT type?

Probably should be TEXT.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[strangeryet]

I tried formatting the Account Number cell as a number, because when it gets imported into the Access table the field is defined as a Long Integer.
However I noticed there is a little green triangle in the upper left corner of the cell in Excel.
Do you know what this means? I suppose that these particular cells are coming through as text.

I then deleted all of these rows but one and I tired to import. Again it failed,(which is what I thought it would do because of the one remaining bad record.)
Then I copied a valid numeric value into this cell and ran it again, and this time it worked.
So you are probably correct, in that it has to do with a text vs. numeric conflict.
But can I format the field on the excell side so that they are all numeric? What is that little green mark?

 

[SkipVought]

I question why AccountNumber should be numeric.

Are you going to do arithemtic on it?

Account Numbers, Phone Numbers, Zip Codes, Part Numbers etc. may have NUMERIC CHARACTERS, but they are not NUMERIC in the sense that you will perform calculations with them.

In reality, they are STRINGS of NUMERIC CHARACTERS -- big difference between a NUMBER and a NUMERIC CHARACTER.

Your Account Number ought to be TEXT with an EDIT to only allow NUMERIC CHARACTERS in the field, perhaps.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[strangeryet]

You know, you are right...let me look into that.

 

[strangeryet]

I changed the field in the table to text, but I am still getting the error. Let me look into this a little deeper.
Thanks

 

[NorthNone]

I had a similar problem when using a Long Integer number field. Turns out that I was exceeding the highest value allowed for Long Integers: 2,147,483,648. It was at that point that I received the error.
The number you show above doesn't have enough digits to qualify, but I thought to add my 2 cents just in case it helped.
A little background. I was taking social security numbers and adding 2,000,000,000 to them for a now obscure reason. Some of the numbers worked fine, others errored out. Turns out when the SSN exceeded 147,483,648 and I added 2,000,000,000 to it, I exceeded the highest number for a Long Integer. Speaking of long, it took me a LONG time to figure this one out...sigh...
HTH
JSouth

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[SkipVought]

JSouth

Why even mess with SSN as a NUMBER?

It is NOT a NUMBER. It is a NUMERIC STRING IDENTIFIER that is never used for arithmetic!

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[NorthNone]

It was a speed issue. Searching through thousands upon thousands of records over a network simply ran faster if I stored them as number instead of text.
I was adding the 1 million or 2 million to differentiate certain types of records.


---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.