need help with expressions that are giving unexpected results

J

Joe

I need some help with this expression

=IIf([Auction Closing Price]>1000,Sum(([Auction Closing
Price]-(1000))*0.015)+(28.12),IIf([Auction Closing Price]>25,Sum(([Auction
Closing Price]-(25))*0.0275)+(1.31),IIf([Auction Closing
Price]>0.01,Sum([Auction Closing Price]*0.0525))))

it seems to work but it also is totaling the field from record to record.
can some one help me out
Joe
 
D

Dirk Goldgar

Joe said:
I need some help with this expression

=IIf([Auction Closing Price]>1000,Sum(([Auction Closing
Price]-(1000))*0.015)+(28.12),IIf([Auction Closing
Price]>25,Sum(([Auction Closing
Price]-(25))*0.0275)+(1.31),IIf([Auction Closing
Price]>0.01,Sum([Auction Closing Price]*0.0525))))

it seems to work but it also is totaling the field from record to
record. can some one help me out
Click to expand...

What are you doing with all those calls to the Sum function in there?
That's going to sum over the records in the recordset. Did you just
want this expression:

=IIf([Auction Closing Price]>1000,
(([Auction Closing Price]-1000)*0.015)+28.12,
IIf([Auction Closing Price]>25,
(([Auction Closing Price]-25)*0.0275)+1.31,
IIf([Auction Closing Price]>0.01,
[Auction Closing Price]*0.0525)))

? I'm not sure that's right, as I just put it together from what you
posted, taking out the calls to Sum() and removing what I *think* are
unnecessary parentheses.
 
J

James A. Fortune

Joe said:
I need some help with this expression

=IIf([Auction Closing Price]>1000,Sum(([Auction Closing
Price]-(1000))*0.015)+(28.12),IIf([Auction Closing Price]>25,Sum(([Auction
Closing Price]-(25))*0.0275)+(1.31),IIf([Auction Closing
Price]>0.01,Sum([Auction Closing Price]*0.0525))))

it seems to work but it also is totaling the field from record to record.
can some one help me out
Joe
Click to expand...

How about:

=DSum("IIf(Nz([Auction Closing Price])>1000,(Nz([Auction Closing
Price])-1000)*0.015+28.12,IIf(Nz([Auction Closing
Price])>25,(Nz([Auction Closing Price])-25)*0.0275+1.31,IIf(Nz([Auction
Closing Price])>0.01,Nz([Auction Closing
Price])*0.0525)))","qryUsedForReportRecordSource")

James A. Fortune
 
Top