Access VBA DCount data type mismatch in criteria expression when data types are obviously the same (-CodePudding

I'm doing a simple DCount, just looking for how many people have signed up for the same date and time. At the moment I'm just using a single date and a single time to prove the process, then I'll have it loop through all possible dates and times.

Private Sub Get_Singles()
    Dim TestDate As String
    Dim TestTime As String
    AloneCnt = 0
    Dinc = 0
    Tinc = 0
    TestDate = vStartDate
    TestTime = "0700"
    
        If (DCount("[ID]", VigilTable, "[fldTime] = " & TestTime) = 1) Then
 '       "[fldDate] = " & TestDate) & " And
        AloneCnt = AloneCnt   1
    End If
End Sub

It works fine for the date (I've moved it to a different line and commented it out so I can focus on the time.).

In the table, fldDate and fldTime are both set for text (shows up as Field Size = 255 in the properties list) and, as you can see, TestDate and TestTime are both dimmed as String.

And it works if I change the DCount line to:

(DCount("[ID]", VigilTable, "[fldTime] = '0700'")

So where's the error?

Thanks.

CodePudding user response：

The error is, that you must handle date and time as data type Date. So change the data type of the fields to DateTime and:

    Dim TestDate As Date
    Dim TestTime As Date
    Dim AloneCnt As Long

    TestDate = vStartDate
    TestTime = TimeSerial(7, 0, 0)
    
    If DCount("*", "VigilTable", "[fldTime] = #" & Format(TestTime, "hh\:nn\:ss") & "# And [fldDate] = #" & Format(TestDate, "yyyy\/mm\/dd") & "#") = 1 Then
        AloneCnt = AloneCnt   1
    End If

CodePudding user response：

Because you hold all date/time bits as text/string, you should use the text syntax in the criteria as you showed: with quotes (notice the quotes around time string):

(DCount("[ID]", VigilTable, "[fldTime] = '" & TestTime & "'")

You could also keep Date/times in date types like @Gustav's answer.