Solved Using DLookup in an access query (1 Viewer)

HealthyB1

Registered User.
Local time
Today, 12:35
Joined
Jul 21, 2013
Messages
82
Hi There I seem to be unable to do a DLookup in a query.
I have a separate database to the one shown and was experimenting on using DLookup in a query. Unfortunately it failed with a message "Undefined function 'DlookUp' in expression"
This has had me stumped for a few days after checking syntax etc to make sure I was right. Still no luck, so I resorted to looking at an example on youtube with the details as per the attached screen shot. "Dlookup_YouTube_Example"
I downloaded the database and proceeded to construct the query as the instructor was demonstrating same in the video.
I have double checked each part of the Dlookup command but when I go to RUN the query I get the error "Undefined function 'DlookUp' in expression"
See second attachment "DLookUp_Error_Msg"

Interestingly if I build a form based on the "tblbookingDetails" and then add a text field called Room Rate and make the control source =DLookUp("[CostPerNight]","tblRooms","[RoomID]=" & [RoomID_FK]) to find the roomrate it works perfectly. I just cannot seem to get the Dlookup to work on a query.

Does anyone have any suggestions please?
 

Attachments

  • Dlookup_youtube_example.PNG
    Dlookup_youtube_example.PNG
    510.9 KB · Views: 222
  • DLookup_Error_Msg.PNG
    DLookup_Error_Msg.PNG
    122.5 KB · Views: 387
  • DlookUP_form.PNG
    DlookUP_form.PNG
    138.9 KB · Views: 173
  • DLookUp_Field_on _orm.PNG
    DLookUp_Field_on _orm.PNG
    105 KB · Views: 337

Gasman

Enthusiastic Amateur
Local time
Today, 04:05
Joined
Sep 21, 2011
Messages
10,843
I can't say why, but it is normally recommended to use a join and not a DlookUp anyway.?
A DLookup can be used in a query.
 

Minty

AWF VIP
Local time
Today, 04:05
Joined
Jul 26, 2013
Messages
9,369
Your syntax is correct. And you can definitely use a DLookUp() in a query.
So something is wrong with your install or that version of Access.

As @Gasman said - in this case you should be bringing in tblRooms and joining it to your data.

Try a compact and repair. Or failing that a decompile or even an office repair.
 

Mike Krailo

Active member
Local time
Yesterday, 23:05
Joined
Mar 28, 2020
Messages
625
Sounds like a missing reference to me. It can happen when opening a database up in different versions of Access. Go to VBE and check if any references are tagged as missing for the current version of Access you are using.
 

HealthyB1

Registered User.
Local time
Today, 12:35
Joined
Jul 21, 2013
Messages
82
Your syntax is correct. And you can definitely use a DLookUp() in a query.
So something is wrong with your install or that version of Access.

As @Gasman said - in this case you should be bringing in tblRooms and joining it to your data.

Try a compact and repair. Or failing that a decompile or even an office repair.
Hi Minty,
I did the compact and repair and no difference. I then checked to see what version of access I was saving the database as and found I only had 2000, 2002-2003 or 2007. I am running Office 2010 on Windows 10.
Decided to move to Windows 365 which allowed me to save it as Access 2007-2016.
So created new blank dBase and imported all tables, queries etc from original version of the database. Saved same and ran Query AOK :)
So problem solved!

Many thanks for your help!
Question how do I mark this problem as SOLVED please?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:05
Joined
Feb 19, 2002
Messages
36,849
Domain functions run separate queries and that is why it is not recommended to use them in queries or inside VBA loops. If you have 1,000 records in your recordset, you will end up running 1,000 dLookups() which is very inefficient.

The recommended solution is to use a query with a left join to the lookup table. You use a left join to handle the situation where the lookup value in your main table is null. If it is NEVER null, then an inner join will work just as well but better safe than sorry so I always use left joins for lookups when I want the main record returned whether or not there is a match in the second table.

Computers are pretty fast so 1,000 queries will run quickly but the larger your recordset and the slower your PC, the more likely you are to see sluggishness when running queries that run domain functions. It is quite possible that you could end up with a situation where you can't actually use a join. That leaves you no alternative but the domain function method. But, if you can use a join, ALWAYS use it in preferance to the domain function if you want to go with best practice methods.
 

Users who are viewing this thread

Top Bottom