DCOUNT by date with a filter box (1 Viewer)

[kacey8]

Evening guys,

I have a DCOUNT on a reporting/stats section of a database. Works fine like it is, current code is

=DCount("[DATE_ENTERED]","[CANVAS_DATA]","[DATE_ENTERED] Between
#" & Format([Forms]![Admin_Reports]![txtfrom],"mm/dd/yyyy") & "# And
#" & Format([Forms]![Admin_Reports]![txtto],"mm/dd/yyyy") & "#")

It counts all entires with the dates entered matches between txtfrom and txtto

Now I've been asked to filter these results down so they can list results via a field called [BRANCH] from a textbox. How would I go about easily adjusting the code to suit?

 

[pbaldy]

Add a third criteria?

http://www.mvps.org/access/general/gen0018.htm

 

[kacey8]

Hi pbaldy,

Thanks, I am working with a third criteria and using the link provided I am getting a syntax error, I am sure it's simple but it's bugging me.

Thanks

=DCount("[DATE_ENTERED]","[CANVAS_DATA]","[DATE_ENTERED] Between
#" & Format([Forms]![Admin_Reports]![txtfrombranch],"mm/dd/yyyy") & "# And
#" & Format([Forms]![Admin_Reports]![txttobranch],"mm/dd/yyyy") & "#" _ & " AND [BRANCH] ='" & [Forms]![Admin_Reports]![totalbranch] & "'")

 

[kacey8]

Well I was completly going about it wrong. This worked beautifully

=DCount("[DATE_ENTERED]","[CANVAS_DATA]","[DATE_ENTERED] >=#" 
& Format([Forms]![Admin_Reports]![txtfrombranch],"mm/dd/yyyy") & "#" 
& " AND [DATE_ENTERED] <=#" & Format([Forms]![Admin_Reports]![txttobranch],"mm/dd/yyyy") & "#" 
& " AND [BRANCH] ='" & [Forms]![Admin_Reports]![totalbranch] & "'")

The date code is better than the between code i had above as the Between would allow it to work backwards (ie if you put the from date in the to box and the to date in the from box. This way it's limited to each other)

Thanks

 

[pbaldy]

Glad you got it sorted.