DLookUp Syntax Error Help

[mapat]

Hello,

I have a table called "ShrinkWrapBinding" where I am supposed to look up a "Price" from. As you see in the code below, I calculate a "totalPackages" number which comes from values in a form. My problem is that when I try to use this value (totalPackages) on the DLookUp function (as seen in this code) it gives me a syntax error exactly on the totalPackages variable. Please note that I added single quotes to the 'totalPackages' variable when I use it on the DLookUp function, and have tried every possible combination (single quotes, double quotes) but still doesn't work. What is it that I'm doing wrong?

Code:
Dim totalPackages As Integer
Dim tempPrice As Double

totalPackages = Me.QuantityBooks1 / Me.ShrinkWrapQty

tempPrice = DLookup("[ShrinkWrapBinding]! [Price]", "ShrinkWrapBinding", "'totalPackages' >= [ShrinkWrapBinding]![RangeField]")


Thank you

 

[neileg]

I don't think you need the quotes, and I would turn the criterion around

tempPrice = DLookup("[ShrinkWrapBinding]! [Price]", "ShrinkWrapBinding", "[ShrinkWrapBinding]![RangeField] <= totalPackages")

Mind you, if there is more than one RangeField value that meets the criterion, you're going to get a random result from the possible values. You're also dimming totalPackages as an integer when it clearly may well be a decimal. You should be rounding down the value to the nearest integer.

 

[Rabbie]

Try

DLookup("Price", "ShrinkWrapBinding", "[ShrinkWrapBinding].[RangeField] <= totalPackages")

 

[gemma-the-husky]

your code was

totalPackages = Me.QuantityBooks1 / Me.ShrinkWrapQty

tempPrice = DLookup("[ShrinkWrapBinding]! [Price]", "ShrinkWrapBinding", "'totalPackages' >= [ShrinkWrapBinding]![RangeField]")

the dlookup is looking in table "ShrinkWrapBinding" for field "Price" where
a field called "totalPackages" is equal to something or other

since there is no field called totalpackages it wont/cant work

so as Neil says, turn it round the other way

so you get

tempPrice = DLookup("[Price]", "ShrinkWrapBinding", [COLOR="Red"]"[RangeField] <= " & totalPackages)[/COLOR]

note the way the last bit is formatted, its slightly different to neils version. totalpackages is a variable and needs to be handled slightly differently, outside the string.

note finally you dont need the extra table qualifiers - you give the domain "ShrinkWrapBinding" as the second parameter, and the other parameters MUST be realted to that domain

 

[mapat]

Thank you for the quick response

There are 2 ranges, I just sent you one (RangeField1) for simplicity, but I do compare it with 2 ranges. The totalPackages is controlled and formatted somewhere else in the program since it can be a decimal so I am not having that problem. However, the "& totalPackages" syntax was the solution that fixed the problem, so that really saved me.

Thanks everyone again

 

[neileg]

note the way the last bit is formatted, its slightly different to neils version. totalpackages is a variable and needs to be handled slightly differently, outside the string.

Thanks for the gentle re-education!

 

[mapat]

Had no intention to offend you neileg. I was just making sure that my scenario was well described, but thanks for your cute statement.

 

[neileg]

Had no intention to offend you neileg. I was just making sure that my scenario was well described, but thanks for your cute statement.

I was responding to gemma pointing out my error!

I'm not easilly offended, anyway

 

[mapat]

So then I apologize, I misunderstood.

Have a good day