Subject: Subquery in a textbox's control source

[Guest]

Hi Folks, hope someone can help.

Based on a value on a report (eg £10,000) I want to Identify the amount of
comission to pay to an adviser. EG, if the value is 10,000, commision = 50;
15,000 then commision = 75 etc. DLookup will not work (I don't think) as I
need to test against two values. My SQL looks a bit like this:

SELECT commissionAmount
FROM payScale
WHERE lowerLimit>[myVariableValue] and upperLimit<[myVariableValue]

Hope that makes a bit of sense!

George

 

[Guest]

Try this

=DLookUp("commissionAmount","payScale","lowerLimit > " & [myVariableValue] &
" and upperLimit < " & [myVariableValue])

 

[Van T. Dinh]

I am not sure of your set-up from your description (SubQuery / TextBox /
value on a report, DLookUp and the posted SQL String: what have they got to
do with each other???) but from the name you used, I think you got the
comparison operators incorrctly. Should they be:

.... WHERE (lowerLimit < [myVariableValue])
And (upperLimit > [myVariableValue])

You should also consider the case where [myVariableValue] is equal to one of
the limits and modify the comparison operators accordingly.

 

[Guest]

Many thanks for your reply Ofer.

Thanks for explaining how this query would work using the dLookup function.
I'll mark your post as answered when I get home. This work pc is locked down
and I can't use activeX. My original sql and the DLookup that solved the
problem are noted below:

SELECT payScale.psPayment, payScale.psLower, payScale.psUpper
FROM payScale
WHERE (((payScale.psLower)<SumOfAmount) AND ((payScale.psUpper)>SumOfAmount));

=DLookUp("[psPayment]","payScale","[psLower] < " &
Reports!transfersAwaitingPayment!SumOfAmount & " and [psUpper] > " &
Reports!transfersAwaitingPayment!SumOfAmount)


George

Try this

=DLookUp("commissionAmount","payScale","lowerLimit > " & [myVariableValue] &
" and upperLimit < " & [myVariableValue])
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

Hi Folks, hope someone can help.

Based on a value on a report (eg £10,000) I want to Identify the amount of
comission to pay to an adviser. EG, if the value is 10,000, commision = 50;
15,000 then commision = 75 etc. DLookup will not work (I don't think) as I
need to test against two values. My SQL looks a bit like this:

SELECT commissionAmount
FROM payScale
WHERE lowerLimit>[myVariableValue] and upperLimit<[myVariableValue]

Hope that makes a bit of sense!

George

 

[Guest]

Thanks for your reply Van T Dinh,

You're correct about my comparison operator, they were the wrong way round.
The correct SQL is noted below. I got this to work using the DLookup
function.

All the best

George

SELECT payScale.psPayment, payScale.psLower, payScale.psUpper
FROM payScale
WHERE (((payScale.psLower)<[sumOfAmount]) AND
((payScale.psUpper)>[sumOfAmount]));

I am not sure of your set-up from your description (SubQuery / TextBox /
value on a report, DLookUp and the posted SQL String: what have they got to
do with each other???) but from the name you used, I think you got the
comparison operators incorrctly. Should they be:

.... WHERE (lowerLimit < [myVariableValue])
And (upperLimit > [myVariableValue])

You should also consider the case where [myVariableValue] is equal to one of
the limits and modify the comparison operators accordingly.

--
HTH
Van T. Dinh
MVP (Access)

Hi Folks, hope someone can help.

Based on a value on a report (eg £10,000) I want to Identify the amount of
comission to pay to an adviser. EG, if the value is 10,000, commision =
50;
15,000 then commision = 75 etc. DLookup will not work (I don't think) as
I
need to test against two values. My SQL looks a bit like this:

SELECT commissionAmount
FROM payScale
WHERE lowerLimit>[myVariableValue] and upperLimit<[myVariableValue]

Hope that makes a bit of sense!

George