Dlookup - error 2471

[ahmed_optom]

Firstly, thanks guys, I have been viewing this forum for months and its been invaluable for me.

Now I have a problem which I have been stuck on for a few days.

Heres the code :

Dim framecost As Single
Dim rightcost As Single
Dim leftcost As Single

framecost = 0
rightcost = 0
leftcost = 0

DoCmd.OpenForm "DispenseTransaction"
Forms![DispenseTransaction]![txtPatientID] = Me.PatientID
Forms![DispenseTransaction]![OrderID] = Me.OrderID
Forms![DispenseTransaction]![Staff] = txtExaminer.Value
Forms![DispenseTransaction]![Description] = "Dispense"
Forms![DispenseTransaction]![TransactionAmount] = Me.Text205.Value
framecost = DLookup("PurchaseCost", "Frames", FrameReference = Me.FrameReference)
rightcost = DLookup("LensCost", "Prices", LensModel = Me.RightLens)

The first dlookup works fine, the second one, doesnt. The error specifically says 2471 , the expression you entered as a query parameter produced this error 'LensCost'.

Now usually to me this means the criteria is a string and i adjust with the apostrophe's. I have tried many combinations of apostrophes, square brackets and speech marks and I just cant get this to work.

In debugging screen, when I hover over me.rightlens, it gives me the correct value, but when I hover over lensmodel, it says empty. Which is not right.

Any ideas?

 

[Minty]

Your syntax is out, try;

framecost = DLookup("PurchaseCost", "Frames", [COLOR="Red"]"[/COLOR]FrameReference = [COLOR="red"]" & Me.[/COLOR]FrameReference)
rightcost = DLookup("LensCost", "Prices", [COLOR="red"]"[/COLOR]LensModel = [COLOR="red"]" & Me.[/COLOR]RightLens)

This assumes both are numeric values, if they are strings add single quotes before and after the escaped Me. form control references.

 

[ahmed_optom]

Minty, thanks.

The first dlookup was working as it was.

The second I re-wrote again as you did and it worked.

 

[Minty]

Minty, thanks.

The first dlookup was working as it was.

Actually it probably isn't really, but as the field names are the same it was falling over the right way to appear to be working.
I would re-write it correctly , as if you refer back to it at a later date you won't have the correct syntax.