# dlookup - calculated field in group footer

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.
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 ?

W

K

#### Klatuu

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

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

W

#### W

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

D

#### Duane Hookom

Your DLookup() requires fields in tblPrices of fldDiscount and fldPrice.
fldMinPrice and fldMaxPrice must be in the report's record source. Is this
correct?

W

#### W

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.

W

D

#### Duane Hookom

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.

W

#### W

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

D

#### Duane Hookom

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".

W

#### W

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