DCOUNT not working properly

[smaier]

Hello, all!

Basically I have a form that will use a DCOUNT to determine how many times an entry occurs in a query based on machine number and date. There is code that will determine what query the DCOUNT will look at to get its answer. The code is as such:

Private Sub SearchButton_Click()

Dim DateToCheck As Date
Dim QuerySource As String


DateToCheck = DateTextBox.Value


If MachineTextBox.Value = 1 Or MachineTextBox.Value = 2 Or MachineTextBox.Value = 3 Then
    QuerySource = "FemcoPMQuery"
    Else
    If MachineTextBox.Value = 11 Then
        QuerySource = "DoosanPMQuery"
        Else
            MsgBox "Please specify which machine to check logs for"
            Exit Sub
    End If
End If


MsgBox "Number of dates that match: " & DCount("[ID]", QuerySource, "[PerformedOn] = #" & DateToCheck & "#")
'MsgBox "Number of dates that match: " & DCount("[ID]", "FemcoPMQuery", "[PerformedOn] = #" & DateToCheck & "#")
'MsgBox "Number of dates that match: " & DCount("[ID]", "DoosanPMQuery", "[PerformedOn] = #" & DateToCheck & "#")


End Sub

I have posted the test example DB as well since I'm thinking the code isn't wrong (though I would be pleased if it were that simple).

If in the form you set the date to 6/10/2021 and the machine number to 11, DCOUNT works properly, stating there are 3 matches in the query for that machine and date. If you change the machine to 1, DCOUNT incorrectly says there are 0 matches in the query for that machine and date (there are 4 if you manually run the FemcoPMQuery and enter 1 as the machine).

Any ideas? It's probably one of those situations where I have been staring at the monitor too loong and am stuck in a loop of incorrect troubleshooting or am overlooking the obvious.

Any help would be greatly appreciated!

[ssanfu]

The only thing I can see is that
FemcoPMLogData has a time associated with the date =>> 6/10/2021 12:31:53 PM

DoosanPMLogData does not have a time associated with the date =>> 6/10/2021
Even though the time is not displayed, in DoosanPMLogData the actual data in the field "PerformedOn" (type date/time) is #6/10/2021 12:00:00AM#[FONT=Calibri]


For the query, FemcoPMQuery, using MachineTextBox = 1 and DateTextBox = #6/10/2021#, the DCount results in 0 because there is no date/time record that is equal to #6/10/2021 12:00:00 AM#.


If you change the VBA from

    MsgBox "Number of dates that match: " & DCount("[ID]", QuerySource, "[PerformedOn] = #" & DateToCheck & "#")

to

    MsgBox "Number of dates that match: " & DCount("[ID]", QuerySource, "[PerformedOn] > #" & DateToCheck & "#")

or

    MsgBox "Number of dates that match: " & DCount("[ID]",  QuerySource, "[PerformedOn] >= #" & DateToCheck & "#")

the DCount msgbox is "Number of dates that match: 4"

[smaier]

The only thing I can see is that
FemcoPMLogData has a time associated with the date =>> 6/10/2021 12:31:53 PM

DoosanPMLogData does not have a time associated with the date =>> 6/10/2021
Even though the time is not displayed, in DoosanPMLogData the actual data in the field "PerformedOn" (type date/time) is #6/10/2021 12:00:00AM#[FONT=Calibri]

Oh my goodness, THANK YOU!!

That's one of the things I thought of when I was trying to figure it out, but when I opened the table, that field only showed the short date (I had originally started with the field being a long date, but decided to change it to short date after I had already made the other tables - there's 6 in the actual DB project. So, even though it showed the table field format as short date, and without clicking/selecting the specific record all I would see is the short date). Once I clicked on the specific record/cell, I could see the time in addition to the date. Once I deleted the times, it worked perfectly.

Thank you very much for the help. I'd been banging my head against a wall for days and had started entertaining the idea of driving up to the Golden Gate Bridge expressly for the purpose of jumping off it.

I can now proceed to enjoy the weekend. I hope you do the same!

Edit: "not proceed" to "now proceed".

[ssanfu]

Happy to help. I only know this because I ran into the same problem many years ago.

Good luck with your project.....