Use VBA to change query criteria

[jmgaddis]

Just wanted a simple way to modify the "Where" Clause of a Query on the fly so that I can just send a Range of Records to an Excel file using Docmd.TransferSpreadsheet.

Searched and found the below Sub Routine from long ago that does the Job.

Wanted to express my Thanks to LittleSmudge... but the old thread is closed.

Hope this post helps others when searching.

Thanks,
John


Actual Thread: thread705-1120020
LittleSmudge (Programmer) 9 Sep 05 5:38
I regularly change the Where clause directly in existing queries 'on the fly' using VB code

This is because they are PassThrough queries linked to a MySQL database and I want to pass the where clause parameters automatically - without user involvement.

I use a globally defined proc as below:-

CODE
Public Sub ParamToPT(strQueryName As String, strClause As String)
Dim strSQL As String
Dim intPosn As Integer

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(strQueryName)

strSQL = qd.SQL
' in case the existing version has been saved with a Where Clause
' Strip the Where clause off the end
intPosn = InStr(strSQL, "WHERE")
If intPosn > 0 Then
strSQL = Left(strSQL, intPosn - 1)
End If
' Now add the new Where clause
strSQL = Trim(strSQL) & " WHERE " & strClause

' now update the query code itself
qd.SQL = strSQL

Set qd = Nothing
Set db = Nothing

End Sub

 

[MichaelRed]

Unless the criteria changes drastically, a simple PARAMETER query will do the job with a bit less drama.




MichaelRed