How can I reference criteria content in query design view 1

[Tianjin]

I have a access query that will generate a product list base on different criteria. I save all the criterias in a seperate table. Is there any way that I can progammably change the criteria? So I don't need copy and paste criteria text into query design view.

Thanks

 

[Remou]

You can create or edit the SQL of a query, for example:

    strSQL = "Select * From tblTable " & DlookUp("Criteria","tblCriteria","ID=5")
    'May be version dependent
    If DLookup("Name", "MSysObjects", "Name= 'tmpQry'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("tmpQry")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("tmpQry", strSQL)
    End If

 

[Tianjin]

Thanks Remou,

It works so amazingly

I was strugglling for days toward to wrong direction. I tried to find a property that could associates with the criteria text in the query design view. Apparently, that does not exist. I wonder if a such object or property exist that can reference to different part of access object.

looks like querys("qry123").fields("fld1").criteria="like 'easychange%'".

Thank you again for you help.

tianjin

 

[Remou]

No, I don't think that there is any way to get the criteria that have been entered against a field in the design grid, though you could grab the Where statement. It you wish to find a query that has a specific word or phrase, you could loop through the query collection and use Instr on the SQL.