change criteria in a query in access using vba

[aman]

Hi guys

I have a query "Query1" in queries tab and now I want to change the criteria in that query. In existing query1 the criteria is "ProjectRef=P4" and I want to change it to P5.

Can anyone please help me how can we change the criteria using vba?

Thanks

 

[CBrighton]

Do you need to change the criteria saved in the query or just access similar data?

And how is the data accessed, will it be viewed as a query or are you looking to dynamically filter a report / form which uses that query?

 

[JANR]

Look in Access help for QueryDef object there are many ways to manipulate this object, but basically something like this:

Dim qdf As DAO.QueryDef
Dim qdfOLD As String
Set qdf = CurrentDb.QueryDefs("Query1")
With qdf
    qdfOLD = .SQL
    .SQL = Replace(.SQL, "ProjectRef='P4'", "ProjectRef='P5'")
    ' Code to do stuff with SQL-string/query
    .SQL = qdfOLD ' Reset SQL to old setting
End With
Set qdf = Nothing

JR

 

[aman]

Thanks JANR for your help. It worked perfectly fine.