Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 09-17-2013, 06:16 AM   #1
Chelle1661
Newly Registered User
 
Join Date: Feb 2013
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Chelle1661 is on a distinguished road
Zero Values and Calculated Fields in Reports

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!

Chelle1661 is offline   Reply With Quote
Old 09-17-2013, 06:39 AM   #2
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,405
Thanks: 11
Thanked 2,282 Times in 2,234 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Zero Values and Calculated Fields in Reports

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.
plog is offline   Reply With Quote
Old 09-17-2013, 07:05 AM   #3
Chelle1661
Newly Registered User
 
Join Date: Feb 2013
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Chelle1661 is on a distinguished road
Re: Zero Values and Calculated Fields in Reports

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?

Chelle1661 is offline   Reply With Quote
Old 09-17-2013, 07:16 AM   #4
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,405
Thanks: 11
Thanked 2,282 Times in 2,234 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Zero Values and Calculated Fields in Reports

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.
plog is offline   Reply With Quote
Old 09-17-2013, 07:22 AM   #5
Chelle1661
Newly Registered User
 
Join Date: Feb 2013
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Chelle1661 is on a distinguished road
Re: Zero Values and Calculated Fields in Reports

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

but still getting invalid syntax message?
Chelle1661 is offline   Reply With Quote
Old 09-17-2013, 07:26 AM   #6
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,405
Thanks: 11
Thanked 2,282 Times in 2,234 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Zero Values and Calculated Fields in Reports

Are you certain its that statement causing it? Do you have others still on the page?
plog is offline   Reply With Quote
Old 09-17-2013, 07:31 AM   #7
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Zero Values and Calculated Fields in Reports

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

__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 09-17-2013, 07:46 AM   #8
Chelle1661
Newly Registered User
 
Join Date: Feb 2013
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Chelle1661 is on a distinguished road
Re: Zero Values and Calculated Fields in Reports

Tried 0 but that did not work either. Always a syntax error!
Chelle1661 is offline   Reply With Quote
Old 09-17-2013, 08:02 AM   #9
Chelle1661
Newly Registered User
 
Join Date: Feb 2013
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Chelle1661 is on a distinguished road
Re: Zero Values and Calculated Fields in Reports

Hope these screenshots will help:

http://www.access-programmers.co.uk/...&pictureid=180
http://www.access-programmers.co.uk/...&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

Last edited by Chelle1661; 09-17-2013 at 08:05 AM. Reason: Clarify field names in screenshots
Chelle1661 is offline   Reply With Quote
Old 09-18-2013, 04:56 AM   #10
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,405
Thanks: 11
Thanked 2,282 Times in 2,234 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Zero Values and Calculated Fields in Reports

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.

plog is offline   Reply With Quote
Reply

Tags
calculated fields , reports , zero values

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Preserve values in calculated fields. paddywack Forms 2 07-16-2011 05:23 AM
Calculated fields in reports rchhvol Reports 16 12-15-2010 10:15 AM
Calculated fields in Access Reports dsh1504 Reports 1 02-28-2008 08:50 AM
putting calculated fields onto reports or other forms quilter Reports 6 04-25-2007 09:15 AM
Help with linking calculated fields to table values brum_rich Forms 1 08-28-2006 11:55 PM




All times are GMT -8. The time now is 05:28 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World