DCOUNT w/Date Criteria

[hhzyousafi]

I can't get the following DCOUNT working for the life of me:

DCount("EmployeeName", "tblAttendance", "EmployeeName = '" & cboEmployeeName.Value & "'" And "AttendanceDate = #" & txtAttendanceDate.Value & "#")​

I have checked this forum along with Google and there seems to be no easy way to do this? I have tried editing the syntax over and over again. I have been able to run a query and get the desired result in that query; however I am unable to call that result in VBA.

 

[pbaldy]

You've made a common mistake. Take out the double quotes on either side of the And.

 

[hhzyousafi]

Hi Paul,

Thank you for the prompt response. For some reason your suggestion didn't work. I tried removed the double quotes before the "And" first and I got an error (i.e. Error 1 screenshot attached for your easy reference) and then I removed the double quotes after the "And" and I got the same error (i.e. Error 2 screenshot attached for your easy reference). When I removed both of them there was no error; however the "DCOUNT" function simply didn't execute.

Regards,

Zohair Yousafi

 

[hhzyousafi]

I am not a big fan of delimiters however here is the solution:

DCount("EmployeeName", "tblAttendance", "[AttendanceDate] = #" & Format(txtAttendanceDate.Value, "mm/dd/yyyy") & "# And [EmployeeName] = '" & cboEmployeeName.Value & "'")

pbaldy was right about the double quotes; however I had to put the field names in brackets otherwise it kept giving me the error I shared in the previous screenshots. Thank you Paul!

 

[pbaldy]

Happy to help! Odd because normally the brackets are only necessary if you have spaces or symbols in the names.

 

[hhzyousafi]

Odd indeed!

 

[Mile-O]

Don't know what sort of employee names you'll be dealing with but I would be inclined to suggest one slight modification.

DCount("EmployeeName", "tblAttendance", "EmployeeName = """ & cboEmployeeName.Value & """ And AttendanceDate = #" & Format(txtAttendanceDate.Value, "mm/dd/yyyy") & "#")

If you put two " together in a string (as "") then they will be read as a single ".

Try it:

MsgBox """"

Should show just ".

The reason I suggest doing this is your DCount could potentially fail when dealing with certain names with apostrophes. Consider, for example, if the EmployeeName was O'Neill or d'Argento. Your method would go from the opening ' to the first one in the name, leaving a redundant one at the end of the criteria, and Access not knowing what to do with it, other than return and error.

By using "" you can, at the very least, offset this potential error without having to do much. And it will save any head-scratching and bewilderment at a later date.

 

[namliam]

The problem wasnt the brackets, in the error message you still have a " to many after the AND ...

DCount("EmployeeName", "tblAttendance", "EmployeeName = '" & cboEmployeeName.Value & "' And AttendanceDate = #" & txtAttendanceDate.Value & "#")

Should work just fine, assuming your txtAttendanceDate is in US format.