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!

Try this:

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

--

Ken Snell
http://www.accessmvp.com/KDSnell/





"misscrf" <misscrf (AT) yahoo (DOT) com> wrote

On Dec 22, 1:27*pm, misscrf <miss... (AT) yahoo (DOT) com> wrote:
What I'd do, if not a monstrous table, is create a module and toss in
the function called AvgCostPerGal. Then in the query builder enter
Avg Cost Per Gal : AvgCostPerGal(any parameters you like to
calculate)

Sometimes all the parenthesis and brackets using IIF()s get eye
numbing in the expression builder. Especially one like yours.

Thanks for the reply. There were some issues with the last ,null. I
had to revise it to this:

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

It changed the amount I get on the report, but it is still not giving
me the right average. I have a group footer that contains the cost of
the car for that time range (whatever was chosen to run the report).
It is the cost without any body work costs. I also have the mileage
for that time range. When I use a calculator to do the cost/gallon it
is not what the formula report shows. I cannot figure this out. and I
am so close to being done with this project!!


On Dec 22, 4:47*pm, "Ken Snell" <kthsneisll... (AT) ncoomcastt (DOT) renaetl>
wrote:

Thanks, Patrick. I don't really understand how I would make a module
to help me here. Can you please give details?


On Dec 22, 8:44*pm, "nos... (AT) thankyou (DOT) com" <miss... (AT) yahoo (DOT) com> wrote:

I also tried this:

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

I then have a control in the detail of the report that is
=Avg(AvgCostPerGal)

On Dec 22, 8:44*pm, "nos... (AT) thankyou (DOT) com" <miss... (AT) yahoo (DOT) com> wrote:

On Dec 22, 7:45*pm, misscrf <miss... (AT) yahoo (DOT) com> wrote:
I'd create a module. Immediately save it. Then I'd create a function

Public Function CostPerGal(intCostType as Integer, _
strCostType As String) As Currency'

'this converts your IIF() into a function
IF intCostType <> 6 then
...calcs
ElseIf strCostType <> 'Gas' then
...calcs
ElseIf ...additional chckes
...calcs
Endif
End Function

Then in the query builder pass the parameters for the function.
Create a column
CPG : CostPerGal(tblCarCost]![FKCostType]...)

Make sure you have a colon separating the column heading (CPG) from
the function

On Thu, 22 Dec 2011 18:00:23 -0800 (PST), misscrf <misscrf (AT) yahoo (DOT) com>
wrote:

Is this an average of an average? If so, it is likely
mathematically garbage.

[snip]

Sincerely,

Gene Wirchenko

someone helped me with the abs part of this. I don't understand how
the absolute value is getting me what i want.

I need [tblCarCost]![CurCostAmount] where [tblCarCost]![FKCostType]<>6
and I need the sum of that CurCost (for all cost types except any that
are fkcosttype = 6) and divide it by the sum of intgallons. The
report is already filtered for car/date.

Does that make sense?

I think these formulas are making it harder than I need!

On Dec 22, 8:44*pm, "nos... (AT) thankyou (DOT) com" <miss... (AT) yahoo (DOT) com> wrote:

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




On Dec 22, 8:44*pm, "nos... (AT) thankyou (DOT) com" <miss... (AT) yahoo (DOT) com> wrote: