Blank record in query produces "#Error"

[NaughtyZute]

In my Fleet Management Application, I'm pulling together three separate queries to then calculate Total Average Cost Per Mile for each Vehicle.
I have a total of 52 active vehicles. Some or all of them may be returned as part of the following queries:

1. Vehicle Gas Cost Per Mile (40 vehicle records)
2. Vehicle Maintenance Cost Per Mile (37 vehicle records)
3. Vehicle Overhead Cost Per Mile (52 vehicle records)

I have a Query that is JustVIDs containing bridges to all the different Primary Keys (V_ID, VIN, Gas Card).

The problem is that if no gas record exists, the query returns #Error. Not all vehicles have Maintenance records, but a blank record there just results in blank. This is what I want gas to do, but it will not. I've tried NZ, IIF...

I can post the SQL if that will help, but due to all the calculations involved in getting to the final Cost per Mile for gas, there are 3 queries. Everything is fine, until I try to force Gas to display a blank if there are no records for that V_ID.

I hope I've stated this clearly. I've tried very hard. Please ask if something is confusing. Thank you.

[code green]

I'm the guessing the query does some calculations,
but where no record exists, the calculation is done on a NULL value.
Try an extra filter

AND [field] IS NOT NULL

This can be in the WHERE clause or ON condition in JOIN

[NaughtyZute]

My problem with trying to evaluate for null is that the field never evaluates to null because it does not exist for that record. What I really seem to need is "If [Avg Gas Cost Per Mile] is nothing"

[NaughtyZute]

Here's what I finally got to work:
IIf([__costCalc_JustVIDs].[V_ID]=[_costGasCalc1_qryCostPerMile].[V_ID],[Avg Cost per Gallon]/[MPG],0)

[NeoPa]

I suspect Code Green is bang on the money here. Somewhere along the line (It's hard to say where as we don't have all the SQL to hand - and if we did I guess it might be pretty complicated anyway) something is calculating with a Null or zero value that can't handle that. Division by zero is a particularly frequently occurring problem when a query is designed with a certain set of data then later is called into play to handle situations where the data is either different or not even there (which in many cases - OUTER JOINs specifically - means that the data referenced simply arrives as a Null).

I suspect a careful checking of the query known to have problems, with the idea in your mind that you may be dealing with Null data, would turn up the culprit. Alternatively, checking the value available as you do, can also work. It may leave you exposed later if you subsequently want to use the query in a similar way, but you should be equally able to bypass the problem at that stage if it does.