I hope someone can help me, I have been struggling with this issue for days - maybe I am overlooking the simple answer but here goes.
I have a main query - Total Haulage - this adds up the customers haulage costs, these haulage costs are calculated in 8 other queries based on a set time frame. The 8 queries produce a number each and the 8 numbers are added together in the main query to product the total haulage. This works perfectly when there is a haulage component for each of the 8 different parts. Some haulage is calculated using tons * price, others loads * price, other on the particular product which has a fixed price per ton or load. Everything is selected on a date field with a criteria of between a start date and an end date. If the haulage is based on loads I simple use Count on an ID field to calculate the number of loads and then multiple by price - all works perfectly when there is at least 1 record for each 8 different customer haulage.
The problem - for my current time period there is no haulage cost applicable for 2 of my 8 queries, these 2 return an empty query and the main query fails. I have tried NZ, LEN, ISEMPTY, ISNULL, IIF but nothing works. Going forward it is possible that any of the 8 component queries could return no records so I need to add a function to all of them including the Total Haulage query as it is also passed on to another calculation.