Dealing with Null values in math expressions

[systemx]

Hi all,

I am in desperate need of some advice. Today I set to work building some math functions in my queries to calculate delays/turnaround times for work, etc.

I have found that where there are null values in my table - the calculation seems to break down.

Let me explain...

In it's simplest form (I have removed some fields and will deal with DATE only, not TIME which is a seperate field) I have the following -

Date_Received
Date_Started
Date_Pended
Date_Recommenced
Date_Completed

I have the following expressions in my query -

Turnaround: Date_Completed - Date_Received
TimePended: Date_Recommenced - Date_Pended
TimeInactive: Date_Started - Date_Received
Workrate: Turnaround - (TimePended + TimeInactive)

The query calculation will only work when all fields contain a value.

For instance, sometimes the work may be started, then completed. Skipping the 'Pended' and 'Recommenced' steps.

Can anyone tell me what I need to include in my maths for Access to treat a null value in the table as zero? Or any other approach.

Please help!!!!!

Thanks

Rob

 

[MStef]

Try with NZ function.
Nz(Date_Recommenced - Date_Pended),0

 

[systemx]

Sorry to post....I have the answer....

I am using

IIf(Value Is Null,0,Value)

Cheers

Rob

EDIT...Thanks MStef...will also try that as it will make things alot simpler