1 component of query returns no result - overall query return no value

[SarahA]

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.

[orange]

Suggest you show us the SQL view of your queries.
Not sure why you have 8 queries, perhaps you could describe the business situation.

[SarahA]

The sql

SELECT QryHaulageOtherSalesTotal1.Haulage, QryHaulageLCC.Haulage, TblAddition.Haulage, [QryHaulageOtherSalesTotal1]![Haulage]+[QryHaulageLCC]![Haulage]+[TblAddition]![Haulage] AS [Haulage Out Costs], QryHaulageOtherSalesWCGL.Haulage, QryHaulageCat1TankBottoms.Haulage, QryHaulageWasteWaterTotal.[Waste Water Haulage], [QryHaulageOtherSalesWCGL]![Haulage]+[QryHaulageCat1TankBottoms]![Haulage]+[QryHaulageWasteWaterTotal]![Waste Water Haulage] AS [Haulage In Costs], [Haulage in Costs]+[Haulage out Costs]+Nz([Disposal Haulage],0) AS [Total Haulage], TblAddition.DateOfAddition, TblDates.FinishDate, QryDisposalWasteWater.[Total Disposal Waste Water], QryDisposalWCGL.[Total Disposal WCGL], QryDisposalCat1TankBottoms.HaulageDisposalCat1, Nz([QryDisposalBagsOthers]![SumOfTotal Waste Out],0)+Nz([QryDisposalCat1TankBottoms]![HaulageDisposalCat1],0)+Nz([QryDisposalWasteWater]![Total Disposal Waste Water],0)+Nz([QryDisposalWCGL]![Total Disposal WCGL],0) AS [Disposal Haulage], QryDisposalBagsOthers.[SumOfTotal Waste Out]
FROM QryHaulageLCC, QryHaulageOtherSalesWCGL, QryHaulageCat1TankBottoms, TblDates, TblAddition, QryHaulageWasteWaterTotal, QryHaulageOtherSalesTotal1, QryDisposalBagsOthers, QryDisposalWasteWater, QryDisposalWCGL, QryDisposalCat1TankBottoms
WHERE (((QryHaulageOtherSalesTotal1.Haulage)>0) AND ((TblAddition.DateOfAddition)=[TblDates]![FinishDate]));

The business

Manufacture and Sales of Petro/Diesel products and by products plus disposal of waste products
Some waste products have to be disposed and there is a haulage charge for this per load or per ton depending on product.
Some waste products have a haulage and actual disposal charge again can be per ton or per load depending on the product
Some sales of manufactured products also incur haulage charges, different rates per customer and again can be per load or per ton depending on contract
Products, waste products and customers all have different contract prices.

In fact there are 11 queries that join together by date to make up the main Total Haulage query, 2 of the sub queries supply date and an additional extra charge for anything else the accountant wishes to add to haulage, e.g. cleaning the yard, refuse is put in a skip and the skip has to be hauled away and disposed in a particular way due to health and safety so this is an "extra" cost not in sales and purchases

I hope this makes sense

Thanks

[SarahA]

Example of the problem

The issue this month is that the Cat1 Tanks did not need to be cleaned, therefore no Disposal cost for Cat1TankBottoms and no haulage cost for Cat1TankBottoms (this waste product has both costs) the rates are per load and both different rates that is why 2 sub queries for 1 line item.
The accountant wishes to see the disposal and haulage charges separately.
As there is no line item for Cat1TankBottoms this month these queries return nothing and as they are part of sub totals and an overall total in the main Total Haulage query this in turn also returns no record/result and my whole process halts.

[davegri]

NZ([QryHaulageCat1TankBottoms].[Haulage])

Use the NZ function for any item that might return a null. In fact, ALL of them.

[SarahA]

Tried that but NZ will work for a few and not others, especially when dealing with Cat1TankBottoms issue, I use the Date Field to count the number of record but when there is no records in the month the query returns nothing

[davegri]

Build your query with vba:

Dim rs as dao.recordset
dim sSQL as string
sSQL = "SELECT "
set rs = currentdb.openrecordset("QryHaulageOtherSalesTotal1")
rs.movefirst
if not rs.eof then
    sSQL = sSQL & "QryHaulageOtherSalesTotal1.Haulage, "
    set rs = nothing
endif
set rs = currentdb.openrecordset ("QryHaulageLCC")
rs.movefirst
if not rs.eof then
    sSQL = sSQL & "QryHaulageLCC.Haulage, "
    set rs = nothing
endif
... and so on

[SarahA]

I will try that, if it works in one area I can repeat it for all the others, I was trying to not use VBA as I want to walk away form this database and leave it so an average access user could maintain it but I think VBA is going to be my only answer.

Many thanks