VBA Dlookup not selecting items from table where certain dates are selected from drop down

Question

I'm creating a database using MS Access, that allows a user to select a person and a week and view the shifts that person is going to do.

Here's my problem, I've got the code working using a DLookup to search for the staff member using the pid, and searching for a particular day as follows:

'Look up shifts
If IsNull(DLookup("Monday", "tblShifts", "[SPID]= " & SPID)) Then
    MsgBox "Sorry missing shifts", vbCritical, "Error"
Else
    selectedDate = Form_frmMain.cboWeekCommencing.Value
    'MsgBox selectedDate 'shows selected date
    MonShift = DLookup("Monday", "tblShifts", "[SPID]= " & SPID & " AND [WeekBegin]= #" & selectedDate & "#")
    TueShift = DLookup("Tuesday", "tblShifts", "[SPID]= " & SPID & " AND [WeekBegin]= #" & selectedDate & "#")
    WedShift = DLookup("Wednesday", "tblShifts", "[SPID]= " & SPID & " AND [WeekBegin]= #" & selectedDate & "#")
    ThurShift = DLookup("Thursday", "tblShifts", "[SPID]= " & SPID & " AND [WeekBegin]= #" & selectedDate & "#")
    FriShift = DLookup("Friday", "tblShifts", "[SPID]= " & SPID & " AND [WeekBegin]= #" & selectedDate & "#")
    SatShift = DLookup("Saturday", "tblShifts", "[SPID]= " & SPID & " AND [WeekBegin]= #" & selectedDate & "#")
End If

Image of form being used in the database:

Selection of table used in DLookup:

From the above image, the date drop-down is populated from the following code on the form load:

thisWeek = Date - Weekday(Date, vbMonday) + 1
nextWeek = Date - Weekday(Date, vbMonday) + 8
weekAfter = Date - Weekday(Date, vbMonday) + 15

cboWeekCommencing.AddItem (thisWeek)
cboWeekCommencing.AddItem (nextWeek)
cboWeekCommencing.AddItem (weekAfter)

These each relate to week commencing 22/05/2017, 29/05/2017, 05/06/2017. When either the list box or drop-down box are changed the top function is called to populate the shifts, however when I select the weeks of either 22/05/2017 and 29/05/2017 it works fine, but when 05/06/2017 is selected the DLookup returns NULL values.

A few things I've tried are changing the weeks to the later weeks, again it works fine if the date is set to 19/06/2017, or 26/06/2017 but again doesn't work when the 12/06/2017 is used as the date.

Everything works fine when just the staff ID is used.

Does anyone have any idea why this might happen?

Erik A Erik A · Accepted Answer · 2017-05-27T20:21:55

That's because Access is interpreting your dates wrong. Dates in Access SQL (and thus DLookUp) should be US format. Since 22/05/2017 in US would be the 22th month, and that doesn't exist, Access guesses that is European date format. But it should always be US format, so 05/06/2017 will be interpreted as the 6th of May (see Allen Browne's documentation).

So change this:

selectedDate = Form_frmMain.cboWeekCommencing.Value

to this:

selectedDate = Format$(Form_frmMain.cboWeekCommencing.Value, "\#mm\/dd\/yyyy\#")

Assuming that cboWeekCommencing is Date/Time type.

VBA Dlookup not selecting items from table where certain dates are selected from drop down

2 Answers