Using Jet SQL with Date functions

[mraetrudeaujr]

I have an SQL statement that I use to populate/build a report and it works beautifully --- however...I forgot to give the user the ability to use any date range that they want and put this on the report also. Here is the SQL statement;

SELECT [STATION],
     Sum(IIf([DISPOSITION]=1,1,0)) AS [VOLUNTARY RETURN],
     Sum(IIf([DISPOSITION]=2,1,0)) AS [WA/NTA],
     Sum(IIf([DISPOSITION]=3,1,0)) AS [TURNED OVER TO OTHER AGENCY]
   FROM tbl_reinstatement
   WHERE STATION Is Not Null
   GROUP BY [STATION]
UNION ALL SELECT "Total",
     Sum(IIf([DISPOSITION]=1,1,0)),
     Sum(IIf([DISPOSITION]=2,1,0)),
     Sum(IIf([DISPOSITION]=3,1,0))
   FROM tbl_reinstatement
   WHERE STATION Is Not Null;

I was thinking that maybe the 'Between' operator would go at the top and the 'And' operator would go in the middle. I have done this many times with a Select query in the Query design view, but not in SQL. I am trying to make to transition to using SQL exclusively because of what I have seen that it can do. Much more logical to look at, and understand. Thanks in advance

 

[lupins46]

Assuming you are limiting records to those between a supplied start and end date, you need to add your date checks to both Where clauses.

WHERE STATION Is Not Null And mydatefield >= [startdate] and mydatefield<= [EndDate]

 

[mraetrudeaujr]

Okay, I tried this but I get a dialog box 'mydatefield', then another one 'startdate', and then the last one 'EndDate'. When I leave the first one blank (mydatefield), but fill in the next two like you normally would finish a dialog "date" box, the query comes back showing only the "Totals" field (blank). When I fill out the 'mydatefield' as (e.g.) 01/01/2005, and then the next one 'startdate' and then finally the 'EndDate' dialog boxes, I get the same totals as the original SQL query. Here is the addition of your suggestions...

SELECT [STATION],
     Sum(IIf([DISPOSITION]=1,1,0)) AS [VOLUNTARY RETURN],
     Sum(IIf([DISPOSITION]=2,1,0)) AS [WA/NTA],
     Sum(IIf([DISPOSITION]=3,1,0)) AS [TURNED OVER TO OTHER AGENCY]
   FROM tbl_reinstatement
   WHERE STATION Is Not Null And mydatefield >= [startdate] and mydatefield<= [EndDate]
   GROUP BY [STATION]
UNION ALL SELECT "Total",
     Sum(IIf([DISPOSITION]=1,1,0)),
     Sum(IIf([DISPOSITION]=2,1,0)),
     Sum(IIf([DISPOSITION]=3,1,0))
   FROM tbl_reinstatement
   WHERE STATION Is Not Null And mydatefield >= [startdate] and mydatefield<= [EndDate];

The whole idea, of course, is to base my final report on this SQL query, giving the enduser the flexibility to enter their desired dates.

 

[mraetrudeaujr]

Nevermind. I finally figured it out. Or at least I figured out how to bring up 'Dialog Boxes' to allow the enduser to query a date range and return the totals. Now all I have to do is figure out how to get their 'Dialog Box' information to insert itself under the Report Title...

Anyways, here is the final SQL that worked.

SELECT [STATION],
     Sum(IIf([DISPOSITION]=1,1,0)) AS [VOLUNTARY RETURN],
     Sum(IIf([DISPOSITION]=2,1,0)) AS [WA/NTA],
     Sum(IIf([DISPOSITION]=3,1,0)) AS [TURNED OVER TO OTHER AGENCY]
     FROM tbl_reinstatement
     WHERE STATION Is Not Null And Date BETWEEN[Enter Beginning Date]And[Enter Ending Date]
     GROUP BY [STATION]
UNION ALL SELECT "Total",
     Sum(IIf([DISPOSITION]=1,1,0)),
     Sum(IIf([DISPOSITION]=2,1,0)),
     Sum(IIf([DISPOSITION]=3,1,0))
FROM tbl_reinstatement
WHERE STATION Is Not Null And Date BETWEEN[Enter Beginning Date]And[Enter Ending Date];

 

[lupins46]

To get the parameter values into the report you will have to include them as fields in your Select and Group by clauses.

SELECT [STATION],[Enter Beginning Date],[Enter Ending Date]
...

GROUP BY [STATION],[Enter Beginning Date],[Enter Ending Date]

and the same thing for the second sub-query.

 

[mraetrudeaujr]

I tried your suggestion, and it didn't work. This could be due to my confusion over the mentioning of a 'sub-query' and not having this inserted here. Do you mean inserting these bracketed values in the "Union" part of this SQL query also? If so, how would this look?

 

[mraetrudeaujr]

I inserted these bracketed values after the Union clause and didn't receive an error. In fact, it seemed to have worked, other than the addition of two columns; "Enter Beginning Date" and "Enter Ending Date". I really don't want to include these values. So I guess I could just 'omit' these columns/values from the final report, right?

 

[lupins46]

SELECT [STATION],[Enter Beginning Date],[Enter Ending Date],
Sum(IIf([DISPOSITION]=1,1,0)) AS [VOLUNTARY RETURN],
Sum(IIf([DISPOSITION]=2,1,0)) AS [WA/NTA],
Sum(IIf([DISPOSITION]=3,1,0)) AS [TURNED OVER TO OTHER AGENCY]
FROM tbl_reinstatement
WHERE STATION Is Not Null And Date BETWEEN[Enter Beginning Date]And[Enter Ending Date]
GROUP BY [STATION],[Enter Beginning Date],[Enter Ending Date]
UNION ALL SELECT "Total",[Enter Beginning Date],[Enter Ending Date],
Sum(IIf([DISPOSITION]=1,1,0)),
Sum(IIf([DISPOSITION]=2,1,0)),
Sum(IIf([DISPOSITION]=3,1,0))
FROM tbl_reinstatement
WHERE STATION Is Not Null And Date BETWEEN[Enter Beginning Date]And[Enter Ending Date];