#Error - How do I turn into a zero?

[access10000]

Hi, I have a query with a 'many to one' join from a table of selected part numbers I want to look at which includes new part numbers with no current order numbers, to a master order table with all order numbers raised against all part numbers. The query returns null values for the new part numbers with no orders. This is what I want to see and the order numbers are stored as text.

I have converted these to numbers using CLng as I need use them in caluculations that only require adding or subtracting - (no DIV#0 issues). However, the nulls are returning '#Error' in the calculated fields which creates a type mismatch issue when filtering or setting other criteria. All fields used in the calculations are numeric.

I do not really have any knowledge of SQL, but is there a way to make the '#Error' values show as zero or something else I could work with.

Any help appreciated.


Thanks

 

[boblarson]

Use the NZ function.

 

[AlanJ10]

I don't think the nz will work, as clng([order]) won't return null it returns #error.
You could use iif([order] is null, 0, clng([order]) instead (replace [order] with whatever your order column is called and 0 with whatever you want it to return when there is no order number.

 

[boblarson]

You need clng(Nz([order],0) BEFORE it tries to convert it.

 

[AlanJ10]

So you do. Sorry, still learning myself.

 

[boblarson]

Sorry, still learning myself.

Hey we all are at some level - don't sweat it. I am currently having an issue which is driving me batty and it's probably something really simple.

 

[access10000]

Great thanks. I will try this tommorrow when I am back at work.

 

[access10000]

Yes, no need to be sorry. Anything that helps move it in the right direction is appreciated. Thanks for all the replies.