IIf Statement

[duesouth]

I've got 3 number fields in a table and in a query I've written a maximum expression to bring back the maximum of these three values.

I want the query to record a null or a number value (as I've discovered 0's cause problems) - so if I do a CDbl expression i.e. CDbl(Maximum) - then nulls in the Maximum field come back as #error, where I want nothing at all.

So what I'm looking for is an IIF statement to either return a null value or the maximum value as a number.

IIF([Maximum]>0,CDbl[Maximum],)

is my guess, but contains invalid syntax - can I build a CDbl statement into an IIF expression?

 

[gemma-the-husky]

you need to use nz to change a null to a number.

nz(somefield,0)

 

[duesouth]

you need to use nz to change a null to a number.

nz(somefield,0)

But won't that still bring back a value where not null that Access won't recognise as a number?

What I want it to return is CDbl(Maximum) OR a null value - as when I use the CDbl expression I get an error rather than a null value.