Multiple Criteria for DLookup using Dates and Strings

[Markb384]

Hi guys

I am trying to use DLookup to test whether a record exists for a certain date for a certain piece of equipment within a table, which is then used to concantanate a string which I later use in a message box. However, I can't seem to get it to work with my test data.

My code is..

Dim sdate As Date
Dim equipID As String, testString As String, msgString As String

'check a date has been selected and if one has, inform the user of which tests were performed on that date
    If IsNull([Forms]![Calculation Results]!
[List_date].Column(0)) Then
        MsgBox ("Please enter a valid date")
        Exit Sub
    Else
        sdate = [Forms]![Calculation Results]!
[List_date].Column(0)
        equipID = [Forms]![Calculation Results]![Equipment_ID]
        testString = "[Equipment ID] = '" & equipID & "' AND [Survey Date] = #" & sdate & "#"
        
        If Not IsNull(DLookup("[Survey Date]", "[Results_Hutt_Dig]", testString)) Then msgString = msgString & "- message1" & vbCrLf
   End If

I have tried to use a single string, as there will be a few of these If Not Null evaluations on several similar tables so it saved typing. I can't seem to get it to work with manually typing into the criteria for DLookup either. Originally, when viewed in the locals windows, sdate was showing the # delimiters, but these disappeared within the string, so I have added them manually. Neither version works unfortunately.



Any ideas? It's probably something really simple and stupid.


Edit: and much searching on the web, I had the idea to ensure that the dates were in the right format (based on a similar solution someone else had). Using format(date,"ddmmyyyy") seemed to fix it. The final code is below now

Dim sdate As DateDim equipID As String, testString As String, msgString As String


'check a date has been selected and if one has, inform the user of which tests were performed on that date
    If IsNull([Forms]![Calculation Results]!
[List_date].Column(0)) Then
        MsgBox ("Please enter a valid date")
    Else
        sdate = [Forms]![Calculation Results]!
[List_date].Column(0)
        equipID = [Forms]![Calculation Results]![Equipment_ID]
        testString = "[Equipment ID] = '" & equipID & "' AND Format([Survey Date],""ddmmyyyy"") = " & Format(sdate, "ddmmyyyy")
        
        If Not IsNull(DLookup("[ID]", "[Results_Hutt_Dig]", testString)) Then msgString = msgString & "- Message1" & vbCrLf
    End If

[amrut]

Access calculations are always based on US date format of mm/dd/yyyy irrespective of your PC setting. Hence, it is always a good practice to format the date as
Format(sdate,"mm\/dd\/yyyy") and with "#" delimeters on either side of date field.
The testring should be like -

testString = "[Equipment ID] = '" & equipID & "' AND [Survey Date]= #" & Format(sdate, "mm\/dd\/yyyy") & "#"

Further, access experts advise to avoid spaces in filed names like [Equipment ID] should be [EquipmentID]
See here for further information about date format in access - http://allenbrowne.com/ser-36.html

[Markb384]

Thank you Amrut, very interesting reading.