dlookup - calculated field in group footer

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

Thanks for your answer,

W

 

[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
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

 

[W]

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

 

[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]

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

 

[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]

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

 

[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]

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