Dynamic SQL ?

[zimmer9]

I have a query that will create a report. The SQL statements that comprise this query are a series of 5 Union statements broken out by date ranges. The last part of each of the SQL statements in each of the Unions are as follows:

The first union is for a date range within the last 6 months:

... WHERE (((C.DateLost) Between #8/1/2004# And #1/31/2005#))

The second union is for a date range from 6 months to 1 year ago:

... WHERE (((C.DateLost) Between #2/1/2004# And #7/31/2004#))
------------------------------------------------------
The user clicks a "Run Report" Command Button and then the application executes the query which generates the report.
I used the following statement to execute the query:

Set rstQueryFS = CurrentProject.Connection.Execute("qryCuPFQ", , adCmdStoredProc)


The issue is that the date ranges I have hard coded need to taken out of the query and replaced by variable date ranges.

When the user runs the report based on this query,
I want to check and see if the current date is the last day of the month. For ex: if today was 1/31/05 then the following dates are acccurate with the 1st sql statement:

... WHERE (((C.DateLost) Between #8/1/2004# And #1/31/2005#))

However, if the current date was other than the last day of the month, for example, if today was 1/14/2005, then I would use the last date of the prior month (for ex: 12/31/2004) as my starting Between date.

... WHERE (((C.DateLost) Between #7/1/2004# And #12/31/2004#))

Instead of using the Passing Parameters feature of Forms in which the user would plug in the current (run) date to allow for the determination of the date ranges within the 5 unions, is there another way to run the report with variable dates for the BETWEEN starting and ending dates within each of the 5 Union statements of my query ?

I was thinking that the Immediate If function could make a comparison of today's date vs. the current month end date as follows:

IIf (Date = (DateSerial(Year(Date),Month(Date)+1,0)

I guess another option would be to breakout the SQL code into a long string and use date variables.

 

[zimmer9]

I use the following code in my Query.

WHERE C.DateLost Between DateSerial(Year(Now())-1,Month(Now())+6,1) And DateSerial(Year(Now()),Month(Now()),0)
GROUP BY C.CustomerNumber, C.DateLost, "LT6";

WHERE C.DateLost Between DateSerial(Year(Now) - 1, Month(Now), 1) And DateSerial(Year(Now) - 1, Month(Now) + 6, 0)
GROUP BY C.CustomerNumber, C.DateLost, "HYRTOYR";

WHERE C.DateLost Between DateSerial(Year(Now) - 2, Month(Now), 1) And DateSerial(Year(Now) - 1, Month(Now), 0)
GROUP BY C.CustomerNumber, C.DateLost, "HYRTOYR";

WHERE C.DateLost Between DateSerial(Year(Now) - 3, Month(Now), 1) AND DateSerial(Year(Now) - 2, Month(Now), 0)
GROUP BY C.CustomerNumber, C.DateLost, "YRTO2YR";

WHERE C.DateLost < DateSerial(Year(Now) - 3, Month(Now), 1)
GROUP BY C.CustomerNumber, C.DateLost, "LE3YR";

 

[MichaelRed]

If the (Run) date is always the last day of the most recently completed month, there should be no need for a parameter, as some relatively simple date arithmatic can provide that. If the user can specify any month (past, current or future), then some parameter will (obviously?) need to be supplied.

How the parameter (if necessary) is supplied depends to some extent on the queries are 'stored'. If they are 'compiled' queries (stored objects) with the SQL posted above, you will need to modify them . The modifications could be to make the date a PARAMETER in hte query and have the parameter based on either Specific User input (input box) or some programmatically accessible object (variable). This could be a text box on a form, a global variable or other method(s). Further details of the overall application and the specifics of the design would, perhaps, suggest an appropiate arrangement.

Alternatively, since the only apparent difference between the snippets shown is the 'assignment' of the text/string identifying the group, the union grouping of the values may actually be quite unnecessary, as the string could reasonable be generated within the parent recordset dynamically at run time?





MichaelRed