Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 1st, 2009, 05:34 PM
Registered User
 
Join Date: Apr 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Getting an Error in using DLookup in a Query

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.
 
Old June 1st, 2009, 05:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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.
================================================== =========
 
Old June 2nd, 2009, 05:03 PM
Registered User
 
Join Date: Apr 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by gbianchi View Post
Hi there..

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

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.
 
Old June 2nd, 2009, 05:13 PM
Registered User
 
Join Date: Apr 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem Solved!

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!!
 
Old June 9th, 2009, 09:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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.

Code:
DLookUp("Vendor_Name","ROR_Vendor","Vendor_Name = '" & [Forms]![Purchase Order]![Vendor_Name] & "'")
If Vendor_Name was a date instead of a string, then

Code:
DLookUp("Vendor_Name","ROR_Vendor","Vendor_Name = #" & [Forms]![Purchase Order]![Vendor_Name] & "#")
If Vendor_Name was a number or boolean (yes/no, true/false), then you'd have

Code:
DLookUp("Vendor_Name","ROR_Vendor","Vendor_Name = " & [Forms]![Purchase Order]![Vendor_Name])
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





Similar Threads
Thread Thread Starter Forum Replies Last Post
DLookup Help jchristadore Access 3 February 20th, 2009 08:55 AM
vb DLOOKUP function and dialog box error bluezcruizer Beginning VB 6 2 January 18th, 2007 04:25 PM
Dlookup function giving Type Mismatch error Ron V Access 2 May 19th, 2004 01:31 PM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.