Solved Using DLookup in an access query

[HealthyB1]

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?

 

[Gasman]

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]

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]

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]

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?

 

[Gasman]

Question how do I mark this problem as SOLVED please?

Should be Solve button top right at the start of your thread.?????