Report Calculations incorrect.....why?

[CBRRider1]

Hello all! I'm new to the forum and need some help. I've created a report from a query that has 2 calculated fields in it. I have the report totals working, but I can't get the average to work. The number that comes out is not what the calculator tells me. The report that is names MONTHLY at the end is the one I'm working on. Can anyone help me out and explain what I did wrong, or how it's coming up with the number it shows?

Thanks,
CBRRider1

MeterDB.zip

[June7]

Why are you using DateAdd to calculate an additional day for the End Date parameter? BETWEEN AND is inclusive for the range ends.

[CBRRider1]

I believe we used that same code for another DB where we wanted the date range to include the days that are entered. 1/1/14 - 2/15/14 would have to 11:59pm. If there is a better way, please let me know. I'm not an expert with Access, but I feel I know a good amount. If it includes it in the 2010 version, that's great, but I know we had an older Access version that needed to have it in there to have reports show the way we wanted. The person that does the reports wants to put in the 1st and last day of the month when viewing the reports (and we aim to please).

[June7]

If I enter 1/30/2014 to 1/30/2014 what I get is 1/30/2014 to 1/31/2014 records.

What I am not understanding is the variance in the average calculation.

Since you have the average calc in the query for each record, just do a Sum on that field (meter_avg) in the report footers.

[CBRRider1]

What he wants is an average for each person for the month and also the average for the month. Total images for each person divided by the active hours. Then the total images for the month divided by the total active hours. You can't sum the average and get the same number. Just doing the math the way it is now doesn't come up right. And I can't figure out why the one person at the bottom comes up with a DIV error when it should be dividing by .5.

[June7]

What is image - each record?

Still don't understand need to add 1 day to EndDate in the query. The VBA procedure that sets form Filter property does not.

If you want monthly summary in the report then maybe add a month/year group. Or is the Monthly report supposed to be filtered to a single month?

I don't see DIV error on MONTHLY report. The DIV error I see is on the Daily report and it is because dividing by 0 ActiveHours.

Sorry about the Sum() suggestion - didn't think that one through.

[CBRRider1]

Nevermind the date problem, I can fix that later. What the end user is tracking is images printed on a production color printer. Each user enters their start and end number along with active and downtime. The MONTHLY report I made is tied to a query that has the total images printed as a calculated field from meter_end - meter_start. Also meter_avg is calculated in the query to be meter_total / active_hours. The dates I used are just so we are looking at the same thing. 1/1/14 - 2/15/14. At the bottom (Sinckler) shows a DIV error when I look at it. Images are 1860 for 0.5 active hours. That should result in a normal number. Just doing the math on the grand total line, (213,594 / 78.25), the good old calculator tells me it's 2729.64. The report says 2738. It seems to be the same for most of them. Amodeo is 85,774 / 25.75. Calc = 3331.03. Report says 3299. How can that be?

As for the supervisor doing the reports, he wants to be able to see daily and monthly totals that include the average per shift and average for the whole month. Let me know if I need to explain anything else. I think I got it all in there.

[June7]

Now I see the DIV error.

I deleted the expression from the oper footer textbox and retyped: =[txt2]/[Text41]

No more DIV error. Weird

[CBRRider1]

Still can't figure out why. And why the division is incorrect. Any ideas? Should I move this to another forum/area?

[June7]

I filtered the report so it returned only one record (Benenati, 1/30/2014).

The calcs agree.

I retyped the formula in the report footer textbox and now that calc is also correct when all records (except OP_Maint) are retrieved.

I've never encountered a textbox ControlSource expression to be corrupted like that, but that is the only explanation I can come up with for both.

Moving the thread won't help. If anyone else had an idea, they would have posted.

[CBRRider1]

Can you send me what you have now? My totals are still wrong. If you at least got that, I'll take it.

Thanks for all your help.

[June7]

Would have been better if you could have fixed yourself but since this is such a weird issue, here it is. I hope it gives you the results I see.

When opening report, just enter past the date popups. I modified the query for default values.

[CBRRider1]

Mark this as solved. I think changing the date in the query fixed the math. Not sure why exactly, but I'll take it. All the calcs look good. Thanks for your help.

CBRRider1