DlookUp not working!

[branston]

Hi.

I have set up some code to do a Dlookup. The code doesnt throw up an error, but it doesnt find the lookup. However, when you put the exact same code in a query and run it with the same example it works! Any ideas why this might be happening?
The code is as below:

stra = Nz(DLookup("[ID No]", "[P Import]", "[Name]='" & Me.[R Name] & "'"), "")
Me.[O Number] = stra
DoCmd.Requery

I have taken over this from someone else, and all the field names have spaces in, which i have avoided in my own designs... Is there something to do with this that might be affecting it?

 

[Galaxiom]

Try:

stra = Nz(DLookup("[ID No]", "[P Import]", "[P Import].[Name]='" & Me.[R Name] & "'"), "")

 

[KenHigg]

Try putting the full reference in the [R Name] part:

stra = Nz(DLookup("[ID No]", "[P Import]", "[Name]='" & forms!MyFormName![R Name] & "'"), "")

Just a quick stab in the dark -

 

[DCrake]

Don't think you need the square brackets around the table name. However just tested it and it worked with or without the [].

 

[branston]

Thanks for all your replies.
DCrake - We were getting a syntax error before and i read somewhere names with spaces can be fixed by putting []'s, so i went a bit crazy and put them everywhere!!
KenHigg - Nope, same problem im afraid.
GalaxiomAtHome - This didnt fix it either im afraid.
Its like im spelling something wrong, but ive checked and double check. If there arent any other suggestions ill check them on monday with a fresh pair of eyes!!

 

[DCrake]

For a start the word Name is an Access reserved word, which won't help, anyway try.

stra = Nz(DLookup("[ID No]", "[P Import]", "[Name]='" & Me("R Name") & "'") ,"")

 

[KenHigg]

Is 'ID No' a numeric data type and if so do you have stra defined as numeric data type?

 

[branston]

stra is defined as a string... ill change that to integer and change the field "Name", see if that works!

Thanks again for all your help... I shall keep you updated!