Why can't I do this?

[Jerry]

Hi,
I'm having trouble accessing a field in a query from a textbox on a
subsubform.
Here's the scheme: form [Customer Orders] -record source query
[qryCustomers] - has a subform [sfmOrders] - record source
table [Orders] - which has a subform [sfmOrderDetails] -
recordsource query [qryOrderDetails].

Quantities and extended prices are totaled in the form footer section of
[sfmOrderDetails].

Table [tblDiscounts] contains a schedule of discounts that apply to
different ranges of values in the [txtQuanTotal] textbox that totals the
Quantity field of the [qryOrderDetails].

Fields of [tblDiscounts]:
[DiscountID] - Autonumber - primary key
[Low] - number - low end of the range for this discount rate
[High] - number - high end of the range for discount
[Discount] - number - discount rate for values between [High] and [Low]

Query [qryDiscounts] returns 1 record from [tblDiscounts] based on the value
in textbox [txtQuanTotal] which totals the Quantity field in all the records
in [qryOrderDetails] for the current order.

My problem is in retrieving the [Discount] value from [qryDiscounts]. In
textbox [txtDiscount], also in the form footer of [sfmOrderDetails].[Form],
I've tried '=[qryDiscounts]![Discount]', and
'=DLookUp("[Discount]","tblDiscounts","([txtQuanTotal] >= [Low]) and
([txtQuanTotal] <= [High])")', and every thing else I can think of. I think
the problem is that [qryDiscounts] is not part of [qryOrderDetails] - the
record source for the subform, but I can't seem to include it without
messing up [qryOrderDetails]. Shouldn't I be able to access the
[qryDiscounts]![Discount] value from an unbound text box in the subform?

 

[Steve Schapel]

Jerry,

Try it like this...
=DLookUp("[Discount]","tblDiscounts","([Low]<=" & [txtQuanTotal] & ")
And ([High]>=" & [txtQuanTotal] & ")")

 

[Jerry]

Thanks, Steve.

That gives me the correct result, but when I first enter it in the Control
Source property line, it complains

"Extra ) in Query expression '([Low] <=) And ([High] >=)' ".

Any idea why? I've tried removing and rearranging the parentheses every way
I can think of as well as wrapping the whole expression in parentheses.
It still goes ahead & works OK with no more complaints after first entering
the expression.

Jerry

Jerry,

Try it like this...
=DLookUp("[Discount]","tblDiscounts","([Low]<=" & [txtQuanTotal] & ")
And ([High]>=" & [txtQuanTotal] & ")")

--
Steve Schapel, Microsoft Access MVP

Hi,
I'm having trouble accessing a field in a query from a textbox on a
subsubform.
Here's the scheme: form [Customer Orders] -record source query
[qryCustomers] - has a subform [sfmOrders] - record source
table [Orders] - which has a subform [sfmOrderDetails] -
recordsource query [qryOrderDetails].

Quantities and extended prices are totaled in the form footer section of
[sfmOrderDetails].

Table [tblDiscounts] contains a schedule of discounts that apply to
different ranges of values in the [txtQuanTotal] textbox that totals the
Quantity field of the [qryOrderDetails].

Fields of [tblDiscounts]:
[DiscountID] - Autonumber - primary key
[Low] - number - low end of the range for this discount rate
[High] - number - high end of the range for discount
[Discount] - number - discount rate for values between [High] and [Low]

Query [qryDiscounts] returns 1 record from [tblDiscounts] based on the value
in textbox [txtQuanTotal] which totals the Quantity field in all the records
in [qryOrderDetails] for the current order.

My problem is in retrieving the [Discount] value from [qryDiscounts]. In
textbox [txtDiscount], also in the form footer of [sfmOrderDetails].[Form],
I've tried '=[qryDiscounts]![Discount]', and
'=DLookUp("[Discount]","tblDiscounts","([txtQuanTotal] >= [Low]) and
([txtQuanTotal] <= [High])")', and every thing else I can think of. I think
the problem is that [qryDiscounts] is not part of [qryOrderDetails] - the
record source for the subform, but I can't seem to include it without
messing up [qryOrderDetails]. Shouldn't I be able to access the
[qryDiscounts]![Discount] value from an unbound text box in the subform?

 

[Steve Schapel]

Hmmm, no I can't see why this would happen.

 

[John Vinson]

That gives me the correct result, but when I first enter it in the Control
Source property line, it complains

"Extra ) in Query expression '([Low] <=) And ([High] >=)' ".

Any idea why? I've tried removing and rearranging the parentheses every way
I can think of as well as wrapping the whole expression in parentheses.
It still goes ahead & works OK with no more complaints after first entering
the expression.

Jerry

Jerry,

Try it like this...
=DLookUp("[Discount]","tblDiscounts","([Low]<=" & [txtQuanTotal] & ")
And ([High]>=" & [txtQuanTotal] & ")")

The error suggests that - at the time you're running the query - there
is nothing in the control named txtQuanTotal.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps