runtime error 2471 Dlookup 1

[davyre]

Hi guys,
i got this error runtime 2471, the expression you entered as a query parameter produces this error: '[Model]'
the code is this

 modelNumber = Worksheet.Cells(excelLine, "B")
        modelNumber = "'" & modelNumber & "'"

 modelID = DLookup("[ModelID]", "TblModel", "[Model] = " & modelNumber & "")

I am 100% sure there is a field named Model in TblModel. What is the issue here? Thanks!
n.b modelNumber is a string variable datatype

 

[davyre]

update:
i changed the code into

modelID=DLookUp("[ModelID]", "TblModel", "[Model]=[COLOR=#CC0000]'[/color]" & modelNumber & "[COLOR=#CC0000]'[/color]")

but still saying error, this time the error message: syntax error (missing operator) in query expression '[Model]="UFWW-1-CB"'".

anyone help? thanks

 

[davyre]

update 2:
I change to something like this:

ModelID = DLookup("ModelID", "TblModel", "ModelNum=' & modelNumber & '")

now it says error invalid use of Null. Whats going on? Checked the modelNumber variable, and it has a value so there should be no usage of Null value

 

[dhookom]

Use some trouble-shooting methods like setting break points and/or Debug.Print.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

How do I use Debug.Print? I already add debug.print below the coding ModelID=Dlookup(..), then when I execute it the immediate window did not show anything, as the compiler stops at the row ModelID=Dlookup(..) (I think)...

 

[dhookom]

Put a break point prior to calling the DLookup and then open the debug window (press Ctrl+G). Find out the value of modelNumber

? modelNumber

Then paste the value into

? DLookup("ModelID", "TblModel", "ModelNum='...Your Value Here...'")

Then try change your code to:

ModelID = DLookup("ModelID", "TblModel", "ModelNum='[b][red]"[/red][/b] & modelNumber & [b][red]"[/red][/b]'")

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

You already have quotes around the field.

modelNumber = [red]"'"[/red] & modelNumber & [red]"'"[/red]

and you are adding more of them in the DLookup

Also, your syntax is incorrect. It should be

DLookup("ModelID", "TblModel", "ModelNum=" & modelNumber )

 

[dhookom]

Good catch Golom. I didn't see the addition of the single quotes since I never use that method.

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

... since I never use that method.

Nor do I.

It just seems wrong to have variable values enclosed in quotes floating around in my code. At best it's confusing and error-prone as in this case.

 

[davyre]

thanks guys, now it's working.