469,293 Members | 1,335 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,293 developers. It's quick & easy.

Blank record in query produces "#Error"

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.
Mar 15 '11 #1
4 11326
code green
1,726 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. AND [field] IS NOT NULL
This can be in the WHERE clause or ON condition in JOIN
Mar 15 '11 #2
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"
Mar 15 '11 #3
Here's what I finally got to work:
IIf([__costCalc_JustVIDs].[V_ID]=[_costGasCalc1_qryCostPerMile].[V_ID],[Avg Cost per Gallon]/[MPG],0)
Mar 15 '11 #4
32,173 Expert Mod 16PB
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.
Mar 16 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by christianlott1 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.