Results 1 to 2 of 2
2005-11-30, 19:18 #1
- Join Date
- Nov 2005
- Br. Columbia, Canada
- Thanked 0 Times in 0 Posts
Queries involving date calculation (Access 2003)
i need to create a query that will calculate current active caseload for a particular program. The variables i have are total number of clients at start, total number of new clients and total number of client cases closed.
To do this, i need to create a query that will count the number of clients entered for a specific program since the start of the database until the last day of month previous to the month you are pulling the report for. I'm not sure how to structure the query to obtain this result. Once i have that number it's a simple matter of using date functions to calculate the other variables.
Im using Access 2003 and must say that i am new to the program, so any help would be appreciated.
2005-11-30, 21:08 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Queries involving date calculation (Access 2003)
Welcome to Woody's Lounge!
I assume that you have a date/time field specifying the date a client entered a program. If you want to restrict to dates up to the last day of last month (before today), you can use the following as criteria for the date field:
If you want to restrict to dates up to the last day of the month before a date specified by the user, you can use the following:
<DateSerial(Year([Enter Date]),Month([Enter Date]),1)
You could replace [Enter Date] with a reference to a text box on a form: [Forms]![frmParameters]![txtDate] or something similar.