M

#### misscrf

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!