Create a centre Criteria for multiple queries 2

[Tianjin]

I have several queries in my access database. All of them have exact same criteria. In some circumstance of changing existing or adding new records, I need to modify all criterias in each query in order to get right result. I wonder whether there is a simply way to just set up a centre criteria in constants or another table from which I can modify them at one time?

Thanks

Tianjin

 

[Remou]

You can built queries from code.

    strSql = "Select * From tblTable "
    strSql = strSql & strCriteria
    If DLookup("Name", "MSysObjects", "Name= 'qryQry'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("qryQry")
        qdf.SQL = strSql
    Else
        Set qdf = CurrentDb.CreateQueryDef("qryQry", strSql)
    End If

 

[Tianjin]

Thank you for the code.
It takes me a quite while to learn Dlookup function. however, those queries in my database are not final queries, instead they could be referenced by other queries. thus, I can not create a new query every time when I need it.
I read help file from microsoft site

http://msdn2.microsoft.com/en-us/library/Aa172176(office.11).aspx,

there is a short expression as below

"You can use the DLookup function to specify criteria in the Criteria row of a query, within a calculated field expression in a query, or in the Update To row in an update query."

It look like I can put such format of criteria as

"Like DLookUp("AssyCriteria","tblAssyCriteria","iD"=1)"

into criteria row to make criteria changeble in a single place. but I can not make it works.

Tianjin

 

[dhookom]

You generally use Like with wildcard characters. This might be what you are looking for.
Like DLookUp("AssyCriteria","tblAssyCriteria","iD=1") & "*"

You could possibly open a hidden form and set the value of a control so your criteria would be something like:
Like Forms!frmInvisCrit!txtCrit & "*"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]