MS Access 2016 Dcount dynamic function

Question

I have a textbox on a form on MS Access 2016 which count no.of present employees for current date based on Dcount formula. I want that formula to be dynamic. How to do that?

Textbox function :

=DCount("[2]","Attendance","[2]='P' And [Manager] = '" & [Label10].[Caption] & "' And [Month] = [Combo1] ")

[2] is date of a month. for example if today's date is 05th Oct'18, it would count present for [5] and so on.

Also I want another Dcount function to count present cases for the whole month

Please help me with that. Thanks in advance !!

It sounds like you have a field called 2 representing the attendance on the 2nd of the month ? This is not how you should be storing an attendance record. And is why you are having issues creating a query / count to give you what you want. I think you need to read up on normalisation. Access is not Excel. — Minty

Gustav Gustav · Accepted Answer · 2018-10-05T10:56:26

Include Date() and correct the syntax:

=DCount("*","Attendance","[2]='P' And [Manager] = '" & [Label10].[Caption] & "' And [Month] = Month(Date())")

However:

[2] is date of a month.

makes no sense when you filter on: [2]='P'

MS Access 2016 Dcount dynamic function

1 Answers