DCount between dates (1 Viewer)

[indyaries]

Greetings,
I am using Access 97 SR2

I have a similar problem I'm trying to address. I'm trying to count the number of employees who have arrived and departed within the past month.

I'm using an unbound form, which is heavily populated with other DCOUNT textboxes.

I need something like this to return the number of employees who departed in Oct 2002:

=DCount("[EmployeeID]","1A_All Employees","[Depart_DNO] Between '09/30/02 And [Depart_DNO]#11/01/02'")

The above does not work, but should show what I'm trying to do.

This report is run at the beginning of the month, and reflects data from the previous month.

Thanks in advance !!

Bob in Indy

 

[Jon K]

Try this:-

=DCount("EmployeeID","1A_All Employees","Depart_DNO between #10/1/02# and #10/31/02#")

 

[indyaries]

Jon,

Worked like a charm !!

In a Report and Form, how would I go about having the user enter these beginning and ending dates and go through the several queries that use beginning and ending dates?

I'm guessing some type of popup form, but not sure how to accomplish the task.

Thanks again!!

Bob in Indy
**************** Edited *******************
Along the lines of the above post, I'm thinking it would be easier if I could set the Form / Report to automatically use dates from the previous month; something like Oct 02, rather than actually inputting beginning and ending dates.

Any thoughts or examples on this are welcome !

 

[Jon K]

In the query on which the form/report is based, set the following in a column in the query grid (using the correct date field name):-

Field: Format([DateFieldName],"mm/yy")
Show: uncheck
Criteria: Format(DateAdd("m",-1,Date()),"mm/yy")


When the form/report is run, it will automatically use dates from the previous month.

 

[indyaries]

Jon...once again...Thanks !!!!!