=DlookUp generating #Error

[3link]

I've tried everything to get this to work. I must be missing some fundamental concept.

I'm attempting to use the DLookup function as an expression in a textbox in a form. I have been placing the following text into the "Control Source" field of the text box.

=DLookUp("[Status]","Ghost","[Subpoena ID] =" & Forms![Ghost2]!Combo)

Ghost is the name of the table.
Status is the name of the results column on the Ghost table.
Subpoena ID is the name of the value column on the Ghost table.
Ghost2 is the name of the form.
Combo is the name of the dropdown box on the Ghost2 form.

So I basically want the textbox to look up the adjacent column ("Status") of the "Subpoena ID" in the dropdown menu (combo). I am consistently getting a #Error message. Can anyone tell me what I'm doing wrong?

 

[Joe4]

Is your "Subpoena ID" field formatted numeric or text?

If text, you will need to use text qualifiers, as shown here under the section called "Specifying Textual Criteria": http://support.microsoft.com/kb/208786

 

[3link]

Is your "Subpoena ID" field formatted numeric or text?

If text, you will need to use text qualifiers, as shown here under the section called "Specifying Textual Criteria": http://support.microsoft.com/kb/208786

Thank you. Subpoena ID is formatted in text.

Just tried this out. I basically took

   =DLookup("[ContactName]", "[Customers]", _
      "[CustomerID]='" & Forms![Orders]![CustomerID] & "'")

from that source to get:

=DLookup("[Status]", "[Ghost]", _
      "[Subpoena ID]='" & Forms![Ghost2]![ComboD] & "'")

When I hit enter after putting this into the control source field, I get an error message:

I've tried it over and over. My code is identical to the examples (minus the names).

 

[xenou]

It can be helpful to debug by "asking" the form what's in the combo: put a button on the form and in it's click event the code:

MsgBox "|" & Forms![Ghost2]![ComboD] & "|"

This should show between two pipes exactly what's in the combobox - so you can check if the criteria you are using is valid.