Dynamic SQL Pass Through Query

[jj.jigga.johns]

I want to create an SQL Pass Through Query that allows me to query a
database for a dynamicly changing date range, say a month prior from
now for instance. Since pass-through queries do not allow for user
entry, is this possible?

I've also tried using SYSDATE. So for my start time I entered
SYSDATE-30 for a month ago and as the end time i used just SYSDATE.
For some reason, this didnt seem to work and only resulted in a day's
worth of records.

Any ideas?

 

[Douglas J. Steele]

You have to dynamically change the SQL.

Assuming you've got a saved pass-through query named MyPassThroughQuery,
you'd use something like:

Dim qdfPassThrough As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Field1, Field2, Field3 FROM MyTable " & _
"WHERE TransactionDate < " & Format(DateDiff("m", -1, Date),
"\'yyyy\-mm\-dd\'")
Set qdfPassThrough = CurrentDb().QueryDefs("MyPassThroughQuery")
qdfPassThrough.SQL = strSQL