Search code examples
ms-accessvbams-access-2016

MS Access 2016 Dcount dynamic function

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 !!

Solution

  • 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'