pass parameter dynamicly to query from form (1 Viewer)

[Wysy]

Hi,
I have a query that is supposed to have dynamic parameter. What i mean is that if i open the query directly there is no parameter set, however if i open it through a command button of a form i would like to be able to set the parameter with a value of a control.
thanks

 

[Dystonia]

If I understand your issue correctly, when you use a form you can

a) Store the non parameterised SQL as a variable

MyVariable = Currentdb.Querydefs("QueryName").SQL

b) Add the parameterised SQL string

Currentdb.Querydefs("QueryName").SQL = "A Text string containing parameterised SQL"

c) when you have finished doing what you need to do return to the status quo

Currentdb.Querydefs("QueryName").SQL = MyVariable

 

[arnelgp]

You need to create a Temporary Query based on the Original and set the parameters there.
Run the Temp Query instead of the Orig:

    Dim strQuery As String
    Dim strSQL As String
    Dim lngGroup As Long
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("tblQuery1")
    strQuery = qdf.Name & " Output"
    ' get the sql sring
    strSQL = qdf.SQL
    Set qdf = Nothing
    ' modify the sql and add parameter here
    strSQL = Replace(strSQL, ";", "")
    ' check if this is Total Query
    lngGroup = InStr(strSQL, "Group By")
    If lngGroup <> 0 Then
        strSQL = Left(strSQL, lngGroup - 1) & " Where (put criteria here) " & Mid(strSQL, lngGroup)
    Else
        strSQL = strSQL & " Where (put criteria here)"
    End If
    On Error Resume Next
    ' delete temporary query
    db.QueryDefs.Delete strQuery
    db.QueryDefs.Refresh
    ' create temporary query
    Set qdf = db.CreateQueryDef(strQuery, strSQL)
    ' add this to collection
    db.QueryDefs.Append qdf
    db.QueryDefs.Refresh
    ' close it
    Set qdf = Nothing
    ' run it
    DoCmd.OpenQuery strQuery