DLOOKUP Always Returning First row of Control Table

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
968
Hello,

I have a table called BookingsT, which will represent a seating plan in my office.

I have created a front end form which will display records and allow users to enter their name to update BookingsT (I dont know how to do this yet :unsure:)

However, step 1 is reflecting the current BookingsT to the form, i read online that DLOOKUP can be used. This is the formula I created for Seat1 textbox
SQL:
=DLookUp("BookingName","BookingsT","BookingSeat=2" And "BookingDate=#" & [tBookingDate] & "#")

However, the first record is always returned. In this case, Seat 2 on 30/10/2020 should return Bill Gates.

DLOOKUP.png


What have i done wrong here?

Your help would be much appreciated
Cheers
Caleeco
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,820
Office Version
  1. 365
Platform
  1. Windows
Very interesting. If I do a single criteria on date like this:
VBA Code:
=DLookUp("BookingName","BookingsT","BookingDate=#" & Format([tBookingDate],"yyyy-mm-dd") & "#")
it seems to handle that criteria correctly (note that I added multiple dates in my sample data).

And if I do single criteria on seat number like this:
VBA Code:
=DLookUp("BookingName","BookingsT","BookingSeat=2")
that works just fine too.

But when I put them both together like this:
VBA Code:
=DLookUp("BookingName","BookingsT","BookingSeat=2" And "BookingDate=#" & Format([tBookingDate],"yyyy-mm-dd") & "#")
it just returns the first record in my table, not matter what. I do not understand why it is doing that!

I know that DLOOKUP can be fluky at times, and people often generally try to avoid using it (I do not use it myself). So you may possibly want to look at other options.
Allen Browne came up with an option that address some of the issues with DLOOKUP here: Microsoft Access tips: Extended DLookup()

Also, if you describe exactly how/where you are trying to incorporate this, we may be able to come up with alternative solutions (like using SQL).
 

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
968
Very interesting. If I do a single criteria on date like this:
VBA Code:
=DLookUp("BookingName","BookingsT","BookingDate=#" & Format([tBookingDate],"yyyy-mm-dd") & "#")
it seems to handle that criteria correctly (note that I added multiple dates in my sample data).

And if I do single criteria on seat number like this:
VBA Code:
=DLookUp("BookingName","BookingsT","BookingSeat=2")
that works just fine too.

But when I put them both together like this:
VBA Code:
=DLookUp("BookingName","BookingsT","BookingSeat=2" And "BookingDate=#" & Format([tBookingDate],"yyyy-mm-dd") & "#")
it just returns the first record in my table, not matter what. I do not understand why it is doing that!

I know that DLOOKUP can be fluky at times, and people often generally try to avoid using it (I do not use it myself). So you may possibly want to look at other options.
Allen Browne came up with an option that address some of the issues with DLOOKUP here: Microsoft Access tips: Extended DLookup()

Also, if you describe exactly how/where you are trying to incorporate this, we may be able to come up with alternative solutions (like using SQL).

Very strange indeed. They return the intended values when I use 1 criteria, but like you, when I use both it gives me the wrong record.

Ok, hopefully this illustration will clarify what Im trying to achieve.
  • Database opens & loads the Form seen attached (with the desk layout showing)
  • Upon opening, it will change date picker to todays date
  • load of form will trigger Control loop to find records for that day and populate text boxes with names if found
    • I assume we can do some sort of loop on control objects to populate using ELOOKUP
  • I will have navigation buttons that let the user move forward or backwards in dates, but also a datepicker to allow quick selection of dates many days in the future.
  • Every time the day changes the form needs to look at BookingsT and populate the text boxes above each desk.

The further complexity will be having a change trigger on each name textbox to WRITE back into the BookingsT (so that employees can book free desks from a visual layout)

I hope that makes sense, happy to clarify if needed. I have attached a sketch of the form & table (which may potentially confuse things further haha)

Thanks for you help looking into this, it's really appreciated
Caleeco
 

Attachments

  • Process.JPG
    Process.JPG
    83.8 KB · Views: 6

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,425
Office Version
  1. 365
Platform
  1. Windows
Then entire criteria clause must be within double quotes. If you need to concatenate characters (such as #) then the result must be as if the entire criteria clause was contained within quotes. You have AND outside of quotes. In effect you have 2 separate parts and I'm surprised it didn't raise an error. Maybe try
SQL:
=DLookUp("BookingName","BookingsT","BookingSeat=2 And BookingDate=#" & Format([tBookingDate],"yyyy-mm-dd") & "#")
 
Solution

JonXL

Active Member
Joined
Feb 5, 2018
Messages
497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Micron beat me to it.

If you're ever having trouble with criteria in this way, throw it in the VBA immediate window preceded by ?. The string it returns must be a valid criteria expression for a SQL WHERE clause to work in DLookup().
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,820
Office Version
  1. 365
Platform
  1. Windows
Thanks for clearing up the mystery, Micron!
I figured it had to be something simple that I was missing.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,425
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I figured it had to be something simple that I was missing.
Blame it on the font color. That light orange is real hard to see - at least for me.
 

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
968
Thanks for clearing up the mystery, Micron!
I figured it had to be something simple that I was missing.

That's the great thing about this forum. The combined knowledge of users is unmatched! (y)
 

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
968
Then entire criteria clause must be within double quotes. If you need to concatenate characters (such as #) then the result must be as if the entire criteria clause was contained within quotes. You have AND outside of quotes. In effect you have 2 separate parts and I'm surprised it didn't raise an error. Maybe try
SQL:
=DLookUp("BookingName","BookingsT","BookingSeat=2 And BookingDate=#" & Format([tBookingDate],"yyyy-mm-dd") & "#")

Ah! It makes sense now that you've said it. These quotes can be tricky!

Thanks for fix!
 

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
968
Micron beat me to it.

If you're ever having trouble with criteria in this way, throw it in the VBA immediate window preceded by ?. The string it returns must be a valid criteria expression for a SQL WHERE clause to work in DLookup().

Awesome, thanks for the tip.

I'm going to try doing this in VBA anyway I think. The control window is pretty laborious to use :LOL:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,825
Messages
5,766,661
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top