Dlookup returning null value and/or runtime error 1

[Newuser26]

I am having a problem with dLookup, I have a Table called detail two of the fields are called product and desc. on my form I have a text box called Prod ref, that I type the product number in called Prod ref and have a button that when clicked it should return the desc from the detail table but its not !?! Im am useing this code :
Desclook = DLookup("[Desc]", "Detail", "[product] = " & Prod_Ref)
Description.Value = Desclook
Debug.Print Desclook

As you can see i am calling debug to find out what it is returning and it keeps coming up with Null.
Some times I will get a run time error but I haven't writen it down
Please can any one help me
Thanks

 

[macleod1021]

First step I would do is add "Prod_Ref" to the debug.print so you can see exactly what's being queried. Once you have that, do the look up manually. If you can find it in the database, it one of two problems. 1) The database connection isn't working correctly; check your connection string. 2) There's special characters in the string that isn't visible(or in the database); make a new entry being very careful to not use special characters (ctrl/alt + keypress) and then try again. 3) Some databases don't handle case universally. Make sure you search for exactly what's in the database.

HTH

 

[Newuser26]

Thanks macleod1021, I've checked what is Prod_ref and it is returning the corect value "964-000-691-000" that has no meaning to you but that is what it is returning. Yes it is in the database, as a test I created a new table (same name and field names) and put the above product in with a desc and it still returned Null value ????.
I looked at access help files and copied there example and it worked fine, so I don't know whats going on.
I will check to make sure there are no hidden characters.

 

[Golom]

Since [product] appears to be a text field you may need

Desclook = DLookup("[Desc]", "Detail", "[product] = [b][COLOR=red]'[/color][/b]" & Prod_Ref[b][COLOR=red] & "'"[/color][/b])

 

[Newuser26]

Thank you Thank you Thank you Golom and macleod1021, Golom you got it it's so easy when you think about it
Cheers all, happy man again !