473,326 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

The Dreaded DLookup

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, 328 views)
Apr 19 '19 #1
5 1400
NeoPa
32,556 Expert Mod 16PB
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])
Apr 20 '19 #2
twinnyfo
3,653 Expert Mod 2GB
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.
Apr 20 '19 #3
NeoPa
32,556 Expert Mod 16PB
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.
Apr 20 '19 #4
Rabbit
12,516 Expert Mod 8TB
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.
Apr 22 '19 #5
NeoPa
32,556 Expert Mod 16PB
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.
Apr 22 '19 #6

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

Similar topics

1
by: KLAU | last post by:
I have a field that retrieves information from an expression in a query. I have used a DLookup function to get the calculated field from the query. However, the relationship is 1-to-many so one...
6
by: JLM | last post by:
What am I missing here? I have a form where I enter a "Class Code". This value corresponds to what sits in table "class code descriptions" along with the "title" of each "class code." Key...
4
by: MLH | last post by:
I have tried using DLookUp in this manner... If DLookUp("","tblClients","='2021234567'") Then MsgBox "Found it!" End If I am wondering if that is a misuse of the DLookUp command? Type...
2
by: ctyrrell | last post by:
I have read with interest the many discussions of the 3048 Error: Cannot open any more databases. I understand how the number of open Table ID's causes the problem. My question has to do with the...
8
by: Christine Henderson | last post by:
I have a problem using the above function in the following simplified circumstance: In the lookup table called "Klms Travelled" I have 3 fields, eg: Receiver Name Receiver Suburb ...
11
by: MLH | last post by:
DLookup("", "tblPreliminaryVINs", "=Forms!frmVINODO!SerialNum") is giving me a Type Mismatch error. That's confusing to me and I don't know how to circumvent it. The field in...
2
by: Don | last post by:
Can someone help me fix my DLookup problem. I'm far from proficiency with Access. I've been creating databases for several years for work with the help of many of you and trial and error. I have...
9
by: | last post by:
In my database I have a 'control table' in which basic info is stored about the application, for instance the application's path and the name of the company that is using it. In all of the...
15
by: rleepac | last post by:
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue This is a table used to...
8
by: JDaly | last post by:
This bombs on the StPL= line. I want it to look up a string value from a table with a criterion of matching another string. In immediate mode the form evaluates correctly with ? Form!! as TEST but...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.