Queries, Null Values, and Iif Functions - oh my!

[Deirdre]

I have a table which has many fields, but I am only interested in 4 of them:
[DriveDate], [StartTime], [Staff], and [Forecast].

Qry_EarlyStaff
Provides me with a set of drives that occur <=11:15am, the sum of staff, and the sum of Forecast

Qry_LateStaff
Provides me with a set of drives that occur >11:15am, the sum of staff, and the sum of Forecast

On some days, there aren't any late staff drives, and some days there aren't and early staff drives.

Qry_Combo
Combines the 2 aforementioned queries. I have set up 2 expressions to replace null fields with zeroes:
SetEarly0: IIf([SumofEarlyStaff] Is Null,0,[SumOfEarlyStaff])
SetLate0: IIf([SumofLateStaff] Is Null,0,[SumOfLateStaff])

This works fine and dandy. However, I am having difficulties with getting a summation of the Forecast. I've tried using the Iif statement like above by setting null fields to zero then creating a new expression adding the summed forecasts. However, if either [SumofEarlyStaff], or [SumofLateStaff]is null, it will not add it. I also tried using the new expression fields like [SetEarlyForecast0]+[SetLateForecast0] to get a sum, but I then get the dreaded aggregate function error.

Your assistance is very much appreciated.

 

[Joe4]

Take a look at the "NZ" function in Access help. I often use this to convert my null values to zero for mathematical purposes.

 

[Deirdre]

Thank you for the response.

I tinkered with it some more and created a solution. It's probably the long way of doing it as it requires the creation of another query, but it works.

I will look up the NZ function for future reference.

Thanks again!