Zero Values and Calculated Fields in Reports

[Chelle1661]

Hi,

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»)

However I keep getting error messages and despite troubleshooting and looking on the internet I can't seem to find a solution to this.

Please help!
:banghead:

 

[plog]

What result should be returned when [VehicleLitres] is 0? That's the one causing the issue--you can't divide by 0. So, you need to test for 0, then work around it:

=Iif([VehicleLitres]<>0 and IsNull([VehicleLitres])= False, [Kilometers]/[VehicleLitres], "N/A")

That will print "N/A" for all values of [VehicleLitres] that will fail the calculation. If you want something different, change that part.

 

[Chelle1661]

Thanks for your response plog,

So, I wanted to test my caluclated field Kilometers for 0 first but I get syntax error message.

=IIf([VehicleReading]-[PreviousReading]<>0 and IsNull([Kilometers])= False, [Kilometers], "N/A")

I put [VehicleReading]-[PreviousReading] instead of [Kilometers]...as this is the expression for Kilometers..

Is that right?

 

[plog]

Just to be safe I would put parenthesis around the expression:

([VehicleReading]-[PreviousReading)

And be sure to replace every instance of [Kilometers] with it. You just replaced the first instance--you have two others in that expression.

 

[Chelle1661]

Tried :
=IIf(([VehicleReading]-[PreviousReading])<>0 and IsNull([VehicleReading]-[PreviousReading])= False, ([VehicleReading]-[PreviousReading]),"N/A")

but still getting invalid syntax message?

 

[plog]

Are you certain its that statement causing it? Do you have others still on the page?

 

[pr2-eugin]

If you replace "N/A" with 0 do you get the same error?

 

[Chelle1661]

Tried 0 but that did not work either. Always a syntax error!

 

[Chelle1661]

Hope these screenshots will help:

http://www.access-programmers.co.uk/forums/album.php?albumid=57&pictureid=180
http://www.access-programmers.co.uk/forums/album.php?albumid=57&pictureid=179

These are two separate vehicles grouped by Week,VehicleCategory and then Vehicle.
At present I only have calc fields in blue in the Vehicle footer to show:
TotalKilometers =Sum([VehicleReading]-[PreviousReading])
KilometersDailyAverage =[Kilometers]/[AccessTotalsVehicleReadingID]
TotalVehicleLitres=Sum([VehicleLitres])
ConsumptionDailyAverage=[Consumption]/[AccessTotalsVehicleReadingID]
As you can see the Vehicle without zeros returns group calc field value..

I have tried so many variations to syntax to get an IIf function to work!!

Data shown in screenshots:
VEHICLEREADING DATE|VEHICLE READING|KILOMETERS|REFUEL LITRES|CONSUMPTION

 

[plog]

I think you need to narrow down the offending calculation.

1. Make a copy of your report.
2. Open that copy in Design View and remove a possible calculation that is causing this.
3. Try and run the report in print preview. If there is an error, go to #2 and remove another calculation.
4. Keep doing 2 & 3 until you've identified the offending calculation.

That will be the one to focus on. Then we can help you with it.