Changing Criteria of a Query using a Macro or Visual Basic

[Ian]

I want to use a Macro or Access code to change the
criteria filter of a query so the query displays
different record when the Macro is executed.

 

[Steve Schapel]

Ian,

As far as I know, this can not be done with a macro.

In VBA, you can open a DAO.QueryDef variable, based on the saved query,
and manipulate its SQL property in code, according to the changed
criteria. But there is probably an easier way

 

[Guest]

Thanks for the reply. I did look at using the QueryDef in
the help menu but am unsure how to use it because there
were no examples. Could you please send me an example how
to change the SQL of a Query using QueryDef.

Thanks
Ian

 

[Steve Schapel]

Ian

Something like this...

Dim qdf As DAO.QueryDef
Dim StrSQL As String
Set qdf = CurrentDb.QueryDefs("NameOfYourQuery")
strSQL = "SELECT blabla FROM yaya WHERE gaga = 'fred'"
qdf.SQL = strSQL
Set qdf = Nothing