How to correct #Num! errors in a query

[accessmatt]

Hello,

I'm using CInt to convert text data to integer in one of my queries, however the result is populating all rows with #Num! and #Error errors. Does anyone have any idea why and more important how to fix?



thanks,

[RuralGuy]

Are you certain all of the source entries are Integers, -32,768 to 32,767?

[accessmatt]

Hello RuralGuy, actually the data I'm converting into integers using CInt is Text. However it all looks like integers, i.e. 12345. It's between that range you cited as well.

[ranman256]

Are you sure THESE are numbers, and not words? That will do it.

[accessmatt]

ranman256, yes they're all numbers. I take back one thing I said though, these numbers tend to range higher than the 32,767. For example, 433373.0, being one example. Could that be the problem?

[RuralGuy]

Try CLng() which covers -2,147,483,648 to 2,147,483,647

[ranman256]

yes. use LONG.

[accessmatt]

Ruralguy and ranman, thank you . CLng took care of half the errors. Any idea how to remove #Error which I think is being thrown because some rows are Null. Thanks!

[accessmatt]

Answering my own question but I bet it's Nz right!?

[ranman256]

Possible. Usu nulls produce null, unless a fucntion is applied. so possible.

[RuralGuy]

Nz() is exactly the right function to use.