Search code examples
ms-accessvbams-access-2010dcount

DCount not finding matching records in Access

I am not getting the correct value for DCount

Table is "Lessons":
ID (auto) pk
InstructorID As Integer
Weekof As Date
StudentID As Integer
Status As Byte

3 records

My function is

Public Function NoRecordsFound(ByVal instructorid As Integer, ByVal weekof As Date, studentid As Integer) As Integer

    Dim strCriteria As String
    strCriteria = "Lessons.[InstructorID] = " & instructorid & " AND Lessons.[WeekOf] = " & weekof & " AND Lessons.[StudentID] = " & studentid

    NoRecordsFound = DCount("*", "Lessons", strCriteria)
End Function

The function is called from the immediate window as:

 :Debug.Print(NoRecordsFound(5, DateValue("10/3/2016"), 17043))

The following select statement does return the correct number of records (should be 1):

SELECT Lessons.[ID], Lessons.[InstructorID], Lessons.[WeekOf], Lessons.[StudentID], Lessons.[Status]
FROM Lessons
WHERE (((Lessons.[InstructorID])=5) AND ((Lessons.[WeekOf])=DateValue("10/3/2016")) AND ((Lessons.[StudentID])=17043));

Can someone help spot the error in my DCount expression?

Solution

  • When building your string argument for the criteria you need to delimit the date value with hash marks (#). Also, to be safe, it should be formatted as an unambiguous yyyy-mm-dd date string:

    strCriteria = "Lessons.[InstructorID] = " & instructorid & " " & _
        "AND Lessons.[WeekOf] = #" & Format(weekof, "yyyy-mm-dd") & "# " & _
        "AND Lessons.[StudentID] = " & studentid