date filters in query

  • Thread starter Thread starter cquinn
  • Start date Start date
C

cquinn

I need to know how to have my query find the Qty used before StartDate.
And then in the next column find Qty used between StartDate and
EndDate. All information is in one table.
 
This example assumes your date field is named "SaleDate".

1. Create a query.

2. Depress the Total button on the Toolbar.
Access adds a Total row to the grid.

3. In the Field row, enter this:
DateRange: IIf([SaleDate] < [StartDate], -1, IIf([SaleDate] <=
[EndDate], 0, 1))
Accept Group By in the Total row.

4. Add Qty to the grid.
In the Total row under this field, choose Sum.

5. Drag your date field into the grid.
In the Total row, choose Where
In the Criteria row:
<= [EndDate]

6. Choose Parameters on the Query menu, and declare your 2 parameters in the
dialog:
StartDate Date/Time
EndDate Date/Time

The query will output -1 for the sales before the date range, and zero for
sales in the date range, with a total for both.

Alternative approach:
You could experiment with using a subquery to get the totals.
That would mean typing an expression such as this into the Field row:
PriorQty: (SELECT Sum(Qty) AS PriorQty
FROM Table1 AS Dupe
WHERE Dupe.SaleDate < #1/1/2005#)
 
Back
Top