By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

The Dreaded DLookup

P: 2
Hello Everyone!

DLookup defiantly has my number and lack of caffeine isn't helping. I'm attempting to do two things, 1) a simple dlookup and 2) a dlookup using two inputs from a user. The first DLookup I am using a linked table to find the employees name and return their phone number. I decided to use the "Control Source" in Access, which looks like the below:
Expand|Select|Wrap|Line Numbers
  1. =DLookUp("Number","PM3","ID = " & [Name])
The only thing that I get is a "#Error" on the form. The "Number" is the phone number that I would like output on my form, PM3 is my table, and ID is my criteria. What am I doing wrong? My PM3 table looks like the "Num" table attached.

[AdminEdit]The rest of the post has been split into another thread that can be found at DLookup & Cross-Tab[/AdminEdit]

I'm a bit lost and my Mt. Dew is running low. Please help.

Attached Images
File Type: png Num.PNG (2.3 KB, 243 views)
4 Weeks Ago #1
Share this Question
Share on Google+
5 Replies

Expert Mod 15k+
P: 31,302
Please post only one question per thread. It quickly gets very messy when you lump multiple questions into the same thread. In this case I've moved the second part of your question to its own thread DLookup & Cross-Tab.

Your first question is, I suspect, falling fould of Number being a reserved word. I always use brackets ([]) around table and field references anyway in order to avoid such problems ever being an issue. I have to assume that your Control named [Name] (another reserved word.) is actually a control that has a numeric value to match the [ID] field. If not then there's far more wrong than just these issues (See Quotes (') and Double-Quotes (") - Where and When to use them).
Expand|Select|Wrap|Line Numbers
  1. =DLookup(Expr:='[Number]','[PM3]','[ID]=' & [Name])
4 Weeks Ago #2

Expert Mod 2.5K+
P: 3,158
I suspect the problem has more to do with a control having he same name as the underlying field, which causes ambiguous confusion with Access. I get the dreaded #Error whenever I forget to rename my controls. The quotes, as originally written “should” work as is.

However, I agree with NeoPa that you should never EVER name a field “Name” or “Number” or any of the other reserved words in Access.
4 Weeks Ago #3

Expert Mod 15k+
P: 31,302
The quotes (") as originally written certainly would work Twinny. I will always try to suggest the most appropriate where I can though. The code in question is handled by the Expression Service which typically is based on SQL. The SQL standard for string quotes is the apostrophe (') or single-quote.

In this case then, it was not a correction per se, but merely how I choose to write it in my suggestion, to give the best advice I can.

NB. Unlike many others, while I always warn of reserved words in members' code, I don't always advise they should necessarily change them. As long as they understand the ramifications of such usage then I see no great problems with their handling it by the use of brackets ([]) around the field or table references. I see this knowledge as more helpful overall as they are very likely to come across such field and table names in their journeys and won't always be in a position to change them. In such a situation knowing how to handle them with the brackets seems to me more useful.
4 Weeks Ago #4

Expert Mod 10K+
P: 12,335
I think the issue is the filter is referring to two different fields, ID and name. Most likely this is a mistake since name in the sample data is a string and ID is a number.
4 Weeks Ago #5

Expert Mod 15k+
P: 31,302
I assumed the Control [Name] was actually a ComboBox returning the [ID]. I could be wrong but it would be a weird mistake to make otherwise.
4 Weeks Ago #6

Post your reply

Sign in to post your reply or Sign up for a free account.