dlookup - calculated field in group footer

  • Thread starter Thread starter W
  • Start date Start date
W

W

Hi all,

I have a report with a numeric calculated field in a group footer, fldPrice.

When its value has been calculated, I want the report to look up a discount
value fldDiscount in table tblPrices.

If e.g. fldPrice = 17, thus > 10 <= 20, the price will be a, if the price is
20 and <= 30, the price will be b.
Click to expand...
In tblPrice the fields are fldMinPrice (numeric) and fldMaxPrice (numeric).

How do I formulate the lookup function, please ?

I have :

dlookup(“fldDiscountâ€; “tblPricesâ€; “fldPrice between “ & fldMinPrice & “
and “ & fldMaxPriceâ€)

There seems to be a problem, however.

Is there a site where I can find extensive documentation about the domain
aggregate functions ?

Thanks for your answer,

W
 
"There seems to be a problem, however"
doesn't give us much to go on. I bit more info and you would get your
answer.

First, the expression should be in the Control Source of a text box, and it
should be preceeded with an equal sign =
If that doesn't correct it, post back and tell us the rest. We have read it
all and are not easily shocked :)
 
Thanks for your answer.

The expression is in the control source of the box, and starts with =.

When I run the report I get a messagebox :

"Enter parameter value "

and it asks for a value for the first field fldMinPrice.

This is the formula I use :

=dlookup("fldDiscount"; "tblPrices"; "[fldPrice] between " & fldMinPrice & "
and " & fldMaxPrice)

So, based on the result of the calculated field fldPrice, my dLookup
function should retrieve a value from the field fldDiscount in the table
tblPrices, based on the between formula.

W
 
Your DLookup() requires fields in tblPrices of fldDiscount and fldPrice.
fldMinPrice and fldMaxPrice must be in the report's record source. Is this
correct?
 
I'm sorry, the fields fldMinPrice and fldMaxPrice are in the table tblPrices.

So, when I reach a certain price, which is between the fldMinPrice and the
fldMaxPrice, I take the corresponding discount from the field fldDiscount.
That is what I want to do.

Thanks for your advice,

W
 
Assuming fldPrice is in your report's record source try:
=dLookup("fldDiscount"; "tblPrices"; [fldPrice] & " between fldMinPrice and
fldMaxPrice")

I would probably try to put tblPrices in the record source query of the
report if possible.
 
Well, actually, no. It is not in my record source. I can't imagine a link
with the record source; this would inevitably multiply the numer of rows. Is
it essential to do so ?

W
 
Did the expression I suggest work? Is that what the "actually, no" is
referring to?

If the DLookup() would only return a single row based on fldPrice being
between two values in tblPrices, then you record source query should not
"multiply the numer of rows".
 
No, I did mess up with the single and double quotes. So I started from
scratch and used an non equi join in sql, which eventually managed to do the
job.

Anyway, thanks a lot having spent your time and energy in trying to help me.

W
 
Back
Top