I have a field that is really giving me a hard time, the data is query from main system, but has the wrong data type...it comes to me as text, and I need to convert to number....this normally isn't a problem for me, but this case is wasting my time...

I've tried so many combinations... I use a make table query to copy accross to another table, but can't use complete conversion to get it accross without error, so I do a partial convert ( dealing with some nulls) and then I query the new table and try to convert to INT but I get the #Error it that field.

eg..

nz(Field)
nz(Field,0)
nz(Field,"0")

Cint(nz(Field,"0"))
Cint(nz(Field,0))

iif(isnull(Field),0,Field)



I feel like I'm going in circles because I'm dealing with Nulls and converting and it should work....I've done it a 1000 times?

iif(Field & "" = "",0,Field)

Give that a go. By the way, that sorts out the Null. Then you can handle the number from that point :)

I see where I went wrong, and you probably would have picked up on it if I had mentioned it....All of the numbers that errored were greater than 32767...it was literally screaming at be when I look in Table design and the field comes across as "Double"... I didn't even clue in.

So this is what worked for me ( I used yours also in the query).....

Expr1: CDbl(nz([Field],0))

Were they whole numbers as well as fractions or just whole numbers. If the later then it would be more prudent to change them to Long Integer.

David

They were whole numbers, I'm afraid I have some bad habits with these numerical data types, probably because if it works I'm happy....for a lot of what I have setup the default is "Integer" and if it comes across as "Text" numbers I just use Cint, I just thought CDbl would be then next one up, but I guess maybe it isn't?

I can see looking in Help Dbl should be used for decimal and uses 8 bytes as opposed to 4 for Long, so thanks for the heads up.

No problem, just spreading the gospel:)

They were whole numbers, I'm afraid I have some bad habits with these numerical data types, probably because if it works I'm happy....for a lot of what I have setup the default is "Integer" and if it comes across as "Text" numbers I just use Cint, I just thought CDbl would be then next one up, but I guess maybe it isn't?

I can see looking in Help Dbl should be used for decimal and uses 8 bytes as opposed to 4 for Long, so thanks for the heads up.

in some languages you get 1 byte numbers.
Any number format may also have to deal with positive negative

so
1 byte = range from 0 to 255, or maybe -128 to +127 (I think!)

2 byte (INTEGER in VBA) = basically 256 squared
so ranges from 0 to 65535, or maybe -32768 to +32767 (I think!)

4 byte (long in VBA) hence 256 to 4th power
so ranges from 0 to 4294967295 - or maybe - 2147483648 to 2147483647

these datatypes are the most efficient for compiler
------------
real number formats are stored in other ways

you have

single
double
currency
decimal (I think)

all of which offer facility to store real (ie fractional) numbers, with varying degrees of accuracy, and varying maximum/minimum numbers within each.

note also that equality testing with real numbers has to be done very carefully

thanks Dave, good to know

Hi Friends,
I have one query of access 2007.
I want the query for comparing data of two tables in access.
And another query for after comparison if data compared then it will be stored in another table. and if not then it will show pending.
Any suggestion?
Thanks in advance.

Hi Friends,
I have one query of access 2007.
I want the query for comparing data of two tables in access.
And another query for after comparison if data compared then it will be stored in another table. and if not then it will show pending.
Any suggestion?
Thanks in advance.

Please create a new thread.