Problem with DlookUp

[zylosan]

I am using this code

If IsNull(DLookup("Sku", "tbl_products_and_sku", "Product_ID = " & thisProductId)) Then
blah blah



thisPrdouctID is a local string that has been set from another usage of Dlookup and is correctly storing the string variable XP-40R-Y-2-CH. Every time i try to run the function I get the error.

Run-time error '3075'

Syntax error (missing operator) in query expression 'Product_ID = XP-40R-Y-2-CH'

I have tried formatting the query portion of the Dlookup multiple ways and keep getting different errors.

[ssanfu]

Strings need to be delimited with quotes.

Try

If IsNull(DLookup("Sku", "tbl_products_and_sku", "Product_ID = '" & thisProductId & "'")) Then

[zylosan]

Which I have already tried based on some sample usage of Dlookup and produces this error

Run-time error '2471'
The Expression you entered as a query parameter produced this error: 'Product_ID'

[ssanfu]

In table "tbl_products_and_sku", is there a field named "Product_ID"?
Is it a text type field?

[zylosan]

Yes their is a field name "Product_ID" and its a short text type field.

[zylosan]

I found the error "Product_ID" was actually "Product _ID"

[zylosan]

How do I mark this as solved?

[ssanfu]

I created a table with the fields "SKU" and "ProductID".
It has one record:
SKU : AAABBB
ProductID : XP-40R-Y-2-CH

Then I ran this code

Public Sub MyTEstSKU()
   Dim tmp As String
   Dim thisProductId As String


   thisProductId = "XP-40R-Y-2-CH"
   

   tmp = DLookup("Sku", "tbl_products_and_sku", "Product_ID = '" & thisProductId & "'")
   'should return a value
   MsgBox tmp

   tmp = IsNull(DLookup("Sku", "tbl_products_and_sku", "Product_ID = '" & thisProductId & "'"))
   'should be false
   MsgBox tmp

   'changed string to something not in the table
   thisProductId = "XP-40R-Y-2-CL"
   tmp = IsNull(DLookup("Sku", "tbl_products_and_sku", "Product_ID = '" & thisProductId & "'"))
   'should be true
   MsgBox tmp

End Sub

The message boxes returned:
AAABBB
FALSE
TRUE


As far as I can tell, the syntax for DLOOKUP() is correct.
Something else is causing the error.

[Dal Jeanis]

With Dlookup, Dcount, etc, I always use the braces. Sometimes it keeps Access from getting confused. Try this -

If IsNull(DLookup("[Sku]", "[tbl_products_and_sku]", "[Product_ID] = '" & thisProductId & "'")) Then

[zylosan]

Thank you both. The dlookup functions correctly now. The problem was that I had a space in my field name where one did not belong. I would like to mark this as solved but I am not sure how to do so.

[Dal Jeanis]

Top of page, under "Thread tools"