Expression in Select Query To accept Null Values

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

New Member
Joined
Dec 9, 2005
Messages
1
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,032
Messages
5,569,764
Members
412,291
Latest member
marypolitan
Top