Expression in Select Query To accept Null Values

[paul29berks]

I have an expression in a select query:

Bal: Val([FirstOfBalance] - FirstOfBalance being a field name from a table.

If a null value is present an #Error message appears. How can I include null values into this expression?

Thanks

Paul

 

[Andrew Fergus]

Hi Paul
Wrap each part that may include a null value inside an Nz function. The Nz function converts null values to zero (assuming this is what you want to do) and the syntax looks like this : Nz([FirstOfBalance])
HTH, Andrew

 

[a9192shark]

Paul,
Try using the function Nz.

Bal: Val(Nz([FirstOfBalance]))

This will convert all Nulls to zero. You could convert them all to a -1 by using Nz([FirstBalance],-1) if you wanted to know that the account had a null first balance.

HTH,

Alan.