Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    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.

    Kat

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    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:
    <code>
    <DateSerial(Year(Date()),Month(Date()),1)
    </code>
    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:
    <code>
    <DateSerial(Year([Enter Date]),Month([Enter Date]),1)
    </code>
    You could replace [Enter Date] with a reference to a text box on a form: [Forms]![frmParameters]![txtDate] or something similar.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •