DLOOKUP - dont run away - please

[BRABUS]

Ok I have this code to (try to) pull some info out of a table depending on the value in my current form. I am just using the message box as a quick way to display my results for the moment.

varx = DLookup("[Folder Location]", "[TABLE - WR & WBS Codes]", "Criteria = '" & Forms![ZZZZZ test form]![WR Number] & "'")
MsgBox varx


The form is called: ZZZZZ test form
The field I am using on this form to set the value to look up is: WR Number
The table I want to pull the info from is: TABLE - WR & WBS Codes
The field I want to pull from the table is: Folder Location


Where have I gone wrong in my syntax as I constantly get a 2001 error

 

[JANR]

Here is a link on correct Dlookup syntax: http://www.mvps.org/access/general/gen0018.htm

JR

 

[DCrake]

Firstly don't use spcial characters, such as & in your table/field names. Secondly you don't need the [] around your table name.

Finally if you calling this from a form then use Me.ControlName as your criterion.

David

 

[BRABUS]

JANR - thanks for the pointer, I had already found that link and couldnt get my ehad around it so asking you all was my last resort.

DCrake - I have renamed the table so no spaces or special characters and have (I thought) altered the code correctly now, but I still get error 2001.

My code now reads as follows

varx = DLookup("FolderLocation", "TABLEWRandWBSCodes", "Criteria = '" & Me.WR_Number & "'")
MsgBox varx


I think I am going to have a nose bleed

 

[DCrake]

Is your field called Criteria and is it a text field? if it is a number field then drop the quotes.

 

[JANR]

If criteria is a number:

Dlookup("ValueFieldInTable", "Tablename", "[CompareFieldInTable]=" & CompareFieldInForm)

If Criteria is a textfield:

Dlookup("ValueFieldInTable", "Tablename", "[CompareFieldInTable]='" & CompareFieldInForm & "'")

JR

 

[BRABUS]

thanks guys, I have now managed to pull a simple text value out.

I have altered the field I want to pull out to try and pull back a hyperlink. When I put the results into a textbox on the form it has a # at the start and end and is no longer a hyper link.

Any suggestions?