DLookup does not return correct value

[GaP42]

I have a problem opening a form filtered to a particular record. The form is opened by obtaining a values for respondentID, QuestionnaireID and ResponseSessionDate in a dialog. When all selections are made these are used to identify the record in ResponseSession using a DLookup. The combination of values obtained is unique.

Using the dialog I can open the form for some records correctly, whereas for others it does not.

The Dlookup is:

        intSessionID = Nz(DLookup("ResponseSessionID", "ResponseSessions", "RespondentID = " & intRespondentID _
                                & " AND QuestionnaireID = " & intQuestionnaireID _
                                & " AND ResponseSessionDate = #" & cboResponseDate & "#"), 0)

Used to open the form as:

DoCmd.OpenForm "frmQSession", , , "[ResponseSessionID] = " & intSessionID

Using tracing, intSessionID is "0" for some records (where the form does not load data) however a valid record id is returned for others. In both cases the 3 criteria passed are valid values. I can't see why in some cases it works in others it does not.
Data in the ResponseSessions table show the record exists.
The query for the target form shows records corresponding to the records in the ResponseSessions table.

Any suggestions?

 

[theDBguy]

Are you able to share a sample copy of your db with test data?

 

[June7]

What is your locale? Using U.S. date style?

 

[GaP42]

Attached is the work in progress db .... an adaptation of a Ken Sheridan sample db

Using dd/mm/yyyy date style format (Australia)

From the menu, select the 4th option, "Edit Responses" to select a questionnaire, respondent and date in the dialog.
The situation is shown by selecting:

Questionnaire: myTest
Respondent: John Black
Date: 25/11/2023 --> record retrieved/ form displayed
Date: 4/12/2023 --> record not found ("0")

Thanks for looking

 

[Josef P.]

Steps to find the bug:

dim WhereCondition as String
WhereCondition = "RespondentID = " & intRespondentID _
                                & " AND QuestionnaireID = " & intQuestionnaireID _
                                & " AND ResponseSessionDate = #" & cboResponseDate & "#"

' vs.
' WhereCondition = "RespondentID = " & intRespondentID _
'                                & " AND QuestionnaireID = " & intQuestionnaireID _
'                                & " AND ResponseSessionDate = #" & Format(cboResponseDate, "yyyy-mm-dd") & "#"

Debug.Print "WhereCondition:"
Debug.Print WhereCondition
Debug.Print "Check with Access query (sql):"
debug.print "select * from ResponseSessions where " & WhereCondition
stop

intSessionID = Nz(DLookup("ResponseSessionID", "ResponseSessions", WhereCondition), 0)

Date: 4/12/2023 --> record not found ("0")

This is April 12, 2023 for SQL

 

[June7]

Regarding use of international date input in filter criteria, review http://allenbrowne.com/ser-36.html

No attachment in your post.

 

[ebs17]

Using dd/mm/yyyy date style format (Australia)

SQL only accepts two formats here:

US format => #mm/dd/yyyy#
ISO format => #yyyy-mm-dd#

Since a date is internally a number, the following would also work depending on the regional settings:
CLng(cboResponseDate)
But that's worse style.

 

[CJ_London]

just to add to the other comments. The reason 25/11/2023 worked is because the sql engine knows there are not 25 months in the year so swaps the day and month around rather than generating an error.

In the immediate window

?#25/11/2023#
25/11/2023

?#04/12/2023#
12/04/2023

 

[GaP42]

Solved: Thank you @Josef P. for some guidance on tracking it down and @June7 for Allen Browne's advice - used to ensure the date selected was compared to the date in the table using the same format .
I had trouble uploading - in a cyclone at the moment (low grade, lot of rain) and in a resort with less than optimal internet capacity - it may have prevented the upload because I am sure I had it attached when posting (@theDBguy) .. anyway - on the way to the nest issue.

dtSessionDate = Format$(Me.cboResponseDate, "\#mm\/dd\/yyyy\#")

 

[Minty]

In general (as someone who works with UK formatted dates) I much prefer using yyyy-mm-dd , as it's completely unambiguous.
It also is the format that SQL server uses, so apart from the escape characters changing I always know what I'm passing around.