DLOOKUP Always Returning First row of Control Table

[Caleeco]

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 )

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

=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.

What have i done wrong here?

Your help would be much appreciated
Cheers
Caleeco

 

[Joe4]

Very interesting. If I do a single criteria on date like this:

=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:

=DLookUp("BookingName","BookingsT","BookingSeat=2")

that works just fine too.

But when I put them both together like this:

=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]

Very interesting. If I do a single criteria on date like this:

=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:

=DLookUp("BookingName","BookingsT","BookingSeat=2")

that works just fine too.

But when I put them both together like this:

=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

 

[Micron]

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

=DLookUp("BookingName","BookingsT","BookingSeat=2 And BookingDate=#" & Format([tBookingDate],"yyyy-mm-dd") & "#")

 

[JonXL]

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]

Thanks for clearing up the mystery, Micron!
I figured it had to be something simple that I was missing.

 

[Micron]

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]

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!

 

[Caleeco]

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

=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]

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