Change where statement in query 3

[WaltLukeIII]

I have a query that controls several printouts. I want to change the where statement to the selected choice in a drop down list.
I can get the value from the drop down box and I know how to write the SQL I just do not know how to change the query and then save it

Thanks


Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[sxschech]

This sample code will take a query and strip off the Where clause and allow you to replace it with a new one and save the result back to the query. You would name "qryMySavedQuery" the actual name of your query.

Private Sub cmdCriteria_Click()
'This sample code will take a query and strip off the Where clause and allow you to replace it with a new one and save the result back to the query.

Dim strloqd As String
Dim loqd As QueryDef

'Use items from the form to generate a where statement and save it back to the query
    
    Set loqd = CurrentDb.QueryDefs("qryMySavedQuery")
        
    strloqd = Left(loqd.SQL, InStr(1, loqd.SQL, "WHERE") - 1) & " WHERE " & Me!txtCriteria & ";"
    

    loqd.SQL = strloqd
    loqd.Close
End Sub

 

[Golom]

sxschech
Yes it will ... but if there were other clauses following WHERE in the original query, they will be lost (for example, Group By, Having or Order By.) If that's the case then you need someting more like:

Dim strloqd As String
Dim loqd As QueryDef
Dim TrailingSQL as String
Dim nGroup As Integer
Dim nHaving As Integer
Dim nOrder As Integer

'Use items from the form to generate a where statement and save it back to the query
    
    Set loqd = CurrentDb.QueryDefs("qryMySavedQuery")
    nGroup = Instr(loqd.SQL, "Group By")
    nHaving = Instr ( loqd.SQL, "Having")
    nOrder = Instr ( loqd, "Order By")
    if nGroup > 0 then
        TrailingSQL = mid ( loqd.SQL, nGroup )
    ElseIf nHaving > 0 Then
        TrailingSQL = mid ( loqd.SQL, nHaving )
    ElseIf nOrder > 0 Then 
        TrailingSQL = Mid ( loqd.SQL, nOrder )
    Else
        TrailingSQL = ""
    End If

    strloqd = Left(loqd.SQL, InStr(1, loqd.SQL, "WHERE") - 1) & _
              " WHERE " & Me!txtCriteria & " " &  TrailingSQL & ";"
    
    loqd.SQL = strloqd
    loqd.Close
End Sub

 

[sxschech]

Thanks for enhancing the code to handle additional where options.

 

[Craig0201]

Or even how about using the parameters collection? Would be so much simpler to maintain!

Craig

 

[Golom]

Craig201
I'm not sure how to use the parameters collection for this.
(Not disagreeing ... just don't understand.)

Can you provide an example?