Average Cost per Gallon - Detail and Report Footer?

[misscrf]

I have a formula, but it is not calculating correctly.

AvgCostPerGal: IIf([txtCarCostType]="Gas",IIf(Sum(Abs([tblCarCost]![FKCostType]<>6)*[tblCarCost]![CurCostAmount])=0,0,IIf(Sum([intGallons])=0,0,Sum(Abs([tblCarCost]![FKCostType]<>6)*[tblCarCost]![CurCostAmount])/Sum([intGallons]))),Null)

This is in the control source query for a report where users open a form and choose the cars and or date criteria. They can choose to get a report of all costs for 1, multiple or all cars in the fleet. They can choose to run the report for year to date, one month, a date range, or all entered data.

The report shows by car, each cost type (gas, maintenance, body work, oil changes etc) the cost. Also shown for just the gas cost type is the mileage, gallons of gas purchased over the time period, average miles/gallon, and average cost/gallon. The average cost/gallon is summed for all costs, without any costs under the cost type of body work.

The average cost/gallon calculation is what is above. The costcostype in the calculation above is 6 for body work.

If the cost type is Gas (we will need this calculation
IIf([txtCarCostType]="Gas",

If the sum of the absolute value of CurcostAmount, without body work (6) is 0 then 0
IIf(Sum(Abs([tblCarCost]![FKCostType]<>6)*[tblCarCost]![CurCostAmount])=0,0,

If the gallons for the car and time range is 0 then 0
IIf(Sum([intGallons])=0,0,

Finally - sum of the absolute value of CurcostAmount, without body work (6) divided by the sum of the gallons.
Sum(Abs([tblCarCost]![FKCostType]<>6)*[tblCarCost]![CurCostAmount])/Sum([intGallons]))),

null if the cost type isn't gas (original iif)
Null)


I have checked some of the results and the sum of the absolute value of CurcostAmount, without body work (6) divided by the sum of the gallons does not match the calculation in the control on the report.

I had this calculation in a text box control on the report, but then I don't know how to get the average for the whole fleet at the report footer.

This is my last issue and then this project is done.

They have checked everything over. This is the last thing that isn't working. It is our xmas wish to finish this project before EOY lol.

Please let me know if I left anything out!

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

after looking over my numbers, I had to change 2 calculations. The numbers still aren't right, but I feel like I am getting closer.

CostNoBod: Sum(DLookUp("CurCostAmount","tblCarCost","[FKCostType]<>6 And PKCarCostID = " & [PKCarCostID]))

AvgCostPerGal: IIf(Sum(DLookUp("CurCostAmount","tblCarCost","[FKCostType]<>6 And PKCarCostID = " & [PKCarCostID]))=0,Null,IIf(Sum([intGallons])=0,Null,Sum(DLookUp("CurCostAmount","tblCarCost","[FKCostType]<>6 And PKCarCostID = " & [PKCarCostID]))/Sum([intGallons])))

If anyone has any ideas, I am all eyes! lol

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

How can you expect to use DLookup() against the entire table when the report can be filtered? I would sum up all of the non FKCostTYpe 6 costs and all of the miles into two separate text boxes in the report footer. Then divide one by the other.

Duane
Hook'D on Access
MS Access MVP