Hello Everyone,

I need help!

I had looked at other threads about DLookup but it doesn't match to the problem I am having.

I wish to use DLookup in a Query to pull up Vendor Information from a table via a fieldname in a Form by using the Vendor Name.

Let me explain:

In my Form (Purchase Order), I have a drop-down field (Vendor_Name). When you select a Vendor, I wish to have the query pull the Vendor Name (also named "Vendor_Name") from the Table (ROR_Vendor).

Here is the query:

DLookUp("Vendor_Name","ROR_Vendor","Vendor_Name = " & [Forms]![Purchase Order]![Vendor_Name])

In testing the query, the parameter value do appear and I am able to enter a Vendor Name (eg. "Test Vendor") but then I receive an error:

Syntax error (missing operator) in query expression 'Vendor_Name=Test Vendor'.

What am I missing?

Please advise. Thank you.

Hi there..

Don't you need ' (simple quotes) around the name (in your example test vendor should look 'Test Vendor')?

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

--------------------------------------------------------

Hi GBianchi,

I had tried that and still received an error. The problem lies with using the actual form's field name.


DLookUp("Vendor_Name","ROR_Vendor",'Vendor_Name = ' & [Forms]![Purchase Order ]![Vendor_Name])

If I am to add simple quotes (' '), do I still use the form name?

Ugh! It looks simple but it is so frustrating!!

Thanks.

Hi Everyone,

The problem has been resolved!!

A co-worker had helped and it is now working.

DLookUp("Vendor_Name","ROR_Vendor","Vendor_Name = '" & [Forms]![Purchase Order]![Vendor_Name] & "'")

GBianichi - I now understand what you were asking me to do.

Thank you!!

Yes, what your co-worker showed you is that different types need different delimiters when used in such a code statement. You discovered that for string or text fields, it should look like.

If Vendor_Name was a date instead of a string, then

If Vendor_Name was a number or boolean (yes/no, true/false), then you'd have

Frankly, if Access is smart enough to error out because the types don't match, it should have be smart enough to find out what type it should have been from the table definition and figured out that Vendor_Name was a string. It's like when you get an error that you forgot to put a closing quote or parenthesis on a statement in VBA. If Access is smart enough to know that, why doesn't it just auto-insert it?

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division