Zero Values and Calculated Fields in Reports

[Chelle1661]

Hi All,

About 2 months ago I posted the following [See below] in an attempt to resolve a #Div/0! error relating to my report. I have still not managed to find a solution as no matter how I typed in the expression I kept getting syntax error messages. Having tried all of the numerous suggestions from extremely helpful forum users I am trying once again to see if I have any luck this time!!

I am trying to get calculated fields in my report to work but zero values in the data are throwing up #Div/0! and #Error! and #Num!

The main data fields I am trying to work with are:
VehicleReading
PreviousReading
VehicleLitres

My aim is to track mileage and consumption for a fleet of vehicles and show daily and weekly averages in mileage and consumption and then to flag 20% increase in consumption.

I have a calculated field in my report named Kilometers,
= [VehicleReading]-[PreviousReading]
and also a calculated field named Consumption,
=[Kilometers]/[VehicleLitres]

My sum and average calculation fields in the group footers are only working where there are values above zero in the Kilometer and VehicleLitres and Consumption fields.

In reality there are some days where there has been no travel, so some records will have a zero for the Kilometers field and there are also days when there has been no refueling so there is a zero for the VehicleLitres field.

I have tried using the expression builder to create an IIf function but to no avail.
I have tried copying the syntax suggested by the Expression builder:
«Expr» IIf («expr». «truepart». «falsepart»)

I know I need to get those zero's to fail in the calculation but as I said before all of the suggested expressions I have tried fail due to syntax!

Many thanks in advance for any suggestions forthcoming!
Chelle
:banghead:

 

[JHB]

Could you post a stripped version of your database, with some sample data, (zip it) + info in which report you have the problem.

 

[Mihail]

Maybe I don't understand what you store in VehicleLiters field.
Are you very sure that the formula for Consumption is [Kilometers]/[VehicleLitres] ?
In my mind should be [Kilometers] * [VehicleLitres]

 

[Chelle1661]

JHB, thanks I will try and find some time to do this!

 

[Chelle1661]

Hi Mihail,

The VehicleLitres field stores the number of refuelling litres per day. Sometimes that is 0.
The Kilometers field stores the mileage travelled per day. Sometimes that is 0.

I will check the original Exel spreadsheet formula to see how that should be converted in my Access expression. [We are trying to get daily usage figures in order to flag increases in fuel consumption].

Thanks for your input!

 

[Chelle1661]

P.S. I have also tried to place the Iif statement in the report's underlying query in the Consumption calc field.

[My calc field is Consumption: [VehicleLitres]/[Kilometers] and the #Error only shows in results where there is a zero in the VehicleLitres field].

I tested this by inserting extra decimal points and changing a 0 litres entry to 0.0001 and the #Error changes to a 0 result in the Consumption field immediately.

 

[JHB]

Can't you use the below, in the calculation?

=IIf([VehicleLitres]=0;0;IIf([Kilometers]=0;0;[Kilometers]/[VehicleLitres]))

 

[Chelle1661]

Where do you think is best? In the Query or the Report calc field Control Source?
Am trying report calc field at present..
Thanks!

 

[Chelle1661]

p.s. am getting so confused with all of this and wondering if any of these #Num and #Errors relate to the integer property in my table [set at single currently]..?

 

[Mihail]

Still my math/physics don't fit to yours formulas.

In your first post you say that Consumption = Kilometers/VehicleLiters
Using the measurement units this end up with a Consumption measured in... Km/L (Km/liters) ???????
This is NOT a Consumption. This formula tell you How many kilometers will run your vehicle if you give him VehicleLiters of fuel.
Or, if VehicleLiter = 1 , this formula will say you how many kilometers will run your vehicle using ONE liter of fuel.

In post #6 you say that Consumption = VehicleLiters/Kilometers (so... What formula is used ???)
Again, using the measurement units, this end up with a Consumption measured in L/Km.
This time, this IS a consumption. The formula say you how many liters of fuel are used by a vehicle if it run a number of Kilometers.
Or, if Kilometers = 1 , this formula will say you how many litters of fuel are used when the vehicle run only ONE Km.

On the other hand (also post #6), you say that an error occur if the VehicleLiters = 0 .
This tell me that you use the first formula (the wrong one) because Access (and math) do not allow to divide by zero.
The second formula (the good one) will raise the same error but... if Kilometers = 0 .

So, lets make one step forward (assuming that we will use the second formula)
The consumption calculated by using the second formula is a relative consumption.
For example, my car consumption is RelativeCnsumption = 6.5 liters / 100 km . This is a car property and you can read this form the car's papers.
If I need to calculate how many liters I need to go from City_1 to City_2 I calculate this way:
Distance = 300 Km (from City_1 to City_2)
RelativeConsumption = 6.5 / 100 [Liters / 100 Km) = 0.065 Liters/Km
Consumption = Distance * RelativeConsumption = 300 Km * 0.065 Liter/Km = 19.5 liters

One more step forward.
Say that you wish to verify if the real RelativeConsumption is equal with the consumption specified on the car's papers. What you have to do ?
1) Measure the fuel level = F1 liters
2) Drive a number of kilometers = n Kilometers
3) Measure the fuel level = F2 liters

Do a computation:
Consumption = (F1 - F2) liters
RelativeConsumption = Consumption / n = (F1 - F2) / n

Is the result OK ? Yes... and No .
If you drive only 10 Kilometers then... NO. You will have a big error.
If you drive 10000 Km (a bit more ore less) then... YES. But you need to refuel many times so, you need to do an addition when calculate the total liters of fuel. The same if you write day by day how many kilometers you drive in that day. Finally you need to do an addition in order to calculate the total numbers of kilometers.

Say that in some days you are too tired to drive. So, in that days the number of kilometers will be zero. Note that you can refuel in that day.
The same, in some days you have enough fuel (so not need to refuel) but you drive some kilometers.

Based on the previous paragraph, if you try to calculate the consumption on each day in order to calculate (at the end of your travel) the total consumption you will have:
DayConsumption = RefuleLitersInDay/KilometersInDay
When you drive but not refuel the consumption will be... ZERO ?!?!?!?!?!?
When you refuel but not drive, the consumption will be .... DIV# error because you will try to divide by ZERO.

So, the consumption should be calculate this way:
Consumption = SUM(Refuell) / SUM(Kilometers)

Using other words... at the end of your travel.

Because I used almost all words from my English I need to return to your DB (and to your issue):
Do a calculation in order to SUM all refueled liters (in a certain date interval): TotalLiters
Do another calculation in order to SUM all kilometers in the same date interval): TotalKilometers
Calculate the relative consumption: RelativeConsumption = TotalLiters/TotalKilometers

That is all my English. Thank you because you give me the chance to use it at whole

Good luck !

 

[Chelle1661]

Thanks for all of that Mihail! Post 6 is correct i.e. Litres/Kilometers

Here is the original formula from the Excel Spreadsheet:
"Usage" field: =IF(E26>0,F26*100/E26,0)
E26 is Kilometers driven between yesterday and today (sometimes 0)
F26 is Litres of Fuel pumped into vehicle between yesterday and today (sometimes 0)

I understand that the company is wanting daily Consumption data from this.
The boss wants to see daily, weekly and monthly reports showing actual and averages for both mileage and consumption but I can't get to that until I get past the Divide by Zero problem of which I am aware. You have hit the nail on the head below!

"Based on the previous paragraph, if you try to calculate the consumption on each day in order to calculate (at the end of your travel) the total consumption you will have:
DayConsumption = RefuleLitersInDay/KilometersInDay
When you drive but not refuel the consumption will be... ZERO ?!?!?!?!?!?
When you refuel but not drive, the consumption will be .... DIV# error because you will try to divide by ZERO".

NOW GREAT NEWS : YOU HAVE SOLVED MY PROBLEM THANK YOU!

I used Usage = SUM(VehicleLitres) / SUM(Kilometers) and hey presto my #Num errors go away in the report! Once again, many thanks!

Would it be possible to rather get the query to produce the calculation result as I think this will help me with my report grouping level calculations?

I have tried this in the query:
Usage: Sum([VehicleLitres])/Sum([Kilometers])
However I get an error message:
You tried to execute a query that does not include the specified expression 'VehicleCategoryID' as part of an aggregate function.
I take this to mean that Access does not know which Vehicle I am asking about (the query is listing the entire fleet's results) so I will need to get the expression and syntax to reference the precise VehicleID (not VehicleCategoryID).
p.s.
This pic shows the Query:
http://www.access-programmers.co.uk/forums/picture.php?albumid=57&pictureid=181

 

[Mihail]

Here is the original formula from the Excel Spreadsheet:
"Usage" field: =IF(E26>0,F26*100/E26,0)
E26 is Kilometers driven between yesterday and today (sometimes 0)
F26 is Litres of Fuel pumped into vehicle between yesterday and today (sometimes 0)

The formula is correct:
If you drive (E26>0) then
Usage = F26/E26 * 100 Liters/Km (the members in a multiplied operation can change their places)
Else if not drive (E26=0) then
Usage = 0 (No consumption)
End If

Would it be possible to rather get the query to produce the calculation result as I think this will help me with my report grouping level calculations?

As I said, just I use all my English, so I need to "refuel" it.
This is why (I think ) I don't understand this question.
Or... because the SQL is not my best friend ?????