Type Conversion Failure - text to number 1

[scottian]

I have a table which i import a file into once a week. For some reason the table will not accept the account number as a number. It will only accept it as text. So my plan is to import to a temp table as text then append to the correct table. So i tried to convert the text field using Format, e.g. - Expr1: Format([MyText],"General Number").
However when i try to run the append query i get "Type conversion failure". Here is an example of the data contained in the field. As far as i can tell these look like perfectly normal numbers and shouldnt be a problem, does anyone have any ideas of what i might do?

4002234003027
4002234001021
4002430196013
4002600115010
4002731004010

Cant think of anything...

 

[JerryKlmns]

Even for a double ?

 

[PHV]

Do you'll make some calculations on those numbers ?
If not (as I guess) then keep them as text.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,


Hmmmmmm. "account number as a number"

What! Are you going to add, subtract, multiply, divise, find a root, raise a power, take a derivative, find the limit, extrapolate, interpolate, whatever?

Is this "NUMBER" not, in fact, an IDENTIFIER, upon which none of these things will ever occur? Could this "NUMBER" not, in principle, also be represented by non numeric characters, if, in the ages to come, you came to the 9999999999999 + 1 instance?

Its an IDENTIFIER represented by numeric characters.

Skip,

_[red][/red]

 

[JerryKlmns]

I wouldn't argue with none of you, but there is a remark in help that : You should use the smallest possible FieldSize property setting because smaller data sizes can be processed faster and require less memory". A double, takes up 8 bytes in storage, a 13-characther IDENTIFIER is grater 5/8 than that. Also searching and joinning on text fields isn't faster?

So I 'm asking your point of view based on huge experience

 

[PHV]

JerryKlmns, the main issue is that Doubles aren't exact values but approximations ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JerryKlmns]

PHV I copy that, but for a (i.e.) 18 digit integer value would we get approximations? Or is there a point of integer value that we start getting approximations?

 

[PHV]

Integer or not doesn't matter.
Single and Double are Real floating point approximations of scientific values.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JerryKlmns]

Thank you PHV. And a * for sharing your knowledge

 

[scottian]

I was told in the past that if the data is a number then it is better to store as a number as the database can handle the data more efficiently, and that a text field takes up more memory than a number or date field. Unfortunately I dont know the difference between double/single etc.
If anyone can help with this id be very happy.

Cant think of anything...

 

[JerryKlmns]

scottian

Single and Double are Real floating point approximations of scientific values as clearly states PHV.
In design view of a table and on numeric field, press F1 at the FieldSize property and you 'll get the difference.

About the size, that is correct for longer than 8 characters values.

But ... we should trust both of them.

 

[PHV]

scottian, in a real database I'd use DECIMAL(13,0) (7 bytes of storage) for your account number.
But in access (JetSQL) I suggest you TEXT(13) as explained in my post stamped 16 Apr 07 9:17.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scottian]

JerryKlmns, thanks for the F1 tip, didnt know about that but i will use it.

PHV, I have read your posts and dont think im ignoring your advice. If you say its best to keep it as text, then thats good enough for me.

Thanks to everyone for their input.

cheers

Cant think of anything...