We help IT Professionals succeed at work.

OPENQUERY Where clause?

SueMos
SueMos asked
on
1,733 Views
Last Modified: 2013-11-27
My users want to be able to select from multiple combo boxes and be able to open queries to export them, I can get the code to a really great strSQL statment (thanks to previous help from an epxert) But I don't know how to get it to pass it to a query they want to open??

Can anyone help?
Thanks!

Private Sub Cmd_ActualsQuery_Click()
On Error GoTo Err_Cmd_ActualsQuery_Click
 
 
 
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
 
 
 
''Constant Select statement for the Query definition
strSQL = "SELECT Tbl_Actuals.* " & _
"FROM Tbl_Actuals"
 
strWhere = "WHERE"
 
strOrder = "ORDER BY tbl_Actuals.UCMG_Program_Code;"
 
 
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.cmb_ActualsYear) Then '<--If the textbox txt_ActualsYear contains no data THEN do nothing
strWhere = strWhere & " (qry_Actuals.Actuals_Year) Like '*" & cmb_ActualsYear & "*'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
 
If Not IsNull(Me.Cmb_Type) Then
strWhere = strWhere & " (qry_Actuals.PS_OVF) Like '*" & Me.Cmb_Type & "*'  AND"
End If
 
If Not IsNull(Me.Cmb_Period) Then
strWhere = strWhere & " (qry_Actuals.Period) Like '*" & Me.Cmb_Period & "*'  AND"
End If
 
 
'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
 
''Pass the SQL to the RecordSource of the sfrms
 
'Me.sfrm_Actuals_OVF.Form.RecordSource = strSQL & " " & strWhere & "AND((Tbl_Actuals.PS_OVF)=""OVF"")  " & strOrder
 
    Dim stDocName As String
 
    stDocName = "qry_Actuals"
    DoCmd.OpenQuery stDocName, acViewNormal, acEdit
 
Exit_Cmd_ActualsQuery_Click:
    Exit Sub
 
Err_Cmd_ActualsQuery_Click:
    MsgBox Err.Description
    Resume Exit_Cmd_ActualsQuery_Click
    
End Sub

Open in new window

Comment
Watch Question

Commented:
Put this code on the command button of your form:

Private Sub cmdOpenQry_Click()
Dim strSQL As String
Dim strOldSQL As String
Dim strQry As String

            strQry = "YourDummyQueryName"
            strSQL = "your strSQL string"

strOldSQL = ChangeSQL(strQry, strSQL)

End Sub

Function ChangeSQL(strQry As String, strSQL As String) As String

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(strQry)
    qd.SQL = strSQL
    ChangeSQL = qd.SQL
    Set qd = Nothing
    Set db = Nothing
End Function
 
I think this code will work for you. You can copy the code within the function and paste it into your code. You will need to set the reference shown in the snippet, of course.
'====================================================
'  requires:
'    reference(s) to:
'       Micrsoft DAO 3.6 Object Library
'====================================================
Public Function CreateQuery()
    Dim qdfNew As DAO.QueryDef
' turn off error handling
On Error Resume Next
    With CurrentDb
        ' delete any temp query
        .QueryDefs.Delete "zsqryTemp"
        DoEvents
        ' create a new temp query
        Set qdfNew = .CreateQueryDef("zsqryTemp")
        DoEvents
        ' set the sql of the new query
        qdfNew.SQL = strSQL & " " & strWhere & "AND((Tbl_Actuals.PS_OVF)=""OVF"")  " & strOrder
        DoEvents
        DoCmd.OpenQuery "zsqryTemp", acViewNormal, acEdit
        DoEvents
        '=== delete the temp query
        '===.QueryDefs.Delete "zsqryTemp"
        DoEvents
        .Close
    End With
    DoEvents
' turn error handling back on
On Error GoTo Err_Cmd_ActualsQuery_Click
End Function

Open in new window

Commented:
or you could create a query with anything in it (it will be changed) , save it and change "YourDummyQueryName" in my code to the name of the query.
my code automatically changes the sql of the query to the strSQL you have saved.

Author

Commented:
adraughn- I tired this but nothing opened?

Cwood I am going to try your now,

Thanks for all of the help!
Option Compare Database
Option Explicit
Private Sub Cmd_ActualsQuery_Click()
 
Dim strSQL2 As String
Dim strOldSQL As String
Dim strQry As String
 
 
 
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
 
 
 
''Constant Select statement for the Query definition
strSQL = "SELECT Tbl_Actuals.* " & _
"FROM Tbl_Actuals"
 
strWhere = "WHERE"
 
strOrder = "ORDER BY tbl_Actuals.UCMG_Program_Code;"
 
 
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.cmb_ActualsYear) Then '<--If the textbox txt_ActualsYear contains no data THEN do nothing
strWhere = strWhere & " (qry_Actuals.Actuals_Year) Like '*" & cmb_ActualsYear & "*'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
 
If Not IsNull(Me.Cmb_Type) Then
strWhere = strWhere & " (qry_Actuals.PS_OVF) Like '*" & Me.Cmb_Type & "*'  AND"
End If
 
If Not IsNull(Me.Cmb_Period) Then
strWhere = strWhere & " (qry_Actuals.Period) Like '*" & Me.Cmb_Period & "*'  AND"
End If
 
 
'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
 
''Pass the SQL to the RecordSource of the sfrms
 
 
            strQry = "qry_Actuals"
            strSQL2 = strSQL & " " & strWhere & " " & strOrder
            strOldSQL = ChangeSQL(strQry, strSQL)
End Sub
Function ChangeSQL(strQry As String, strSQL As String) As String
 
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(strQry)
    qd.SQL = strSQL
    ChangeSQL = qd.SQL
    Set qd = Nothing
    Set db = Nothing
End Function
    
    
    

Open in new window

Commented:
before the end sub just add the code to open your query.

Docmd.openquery "queryname"

Author

Commented:
Same thing happened with Cwoods- Clearly I am a missing a step in getting the query to open?!?!
Private Sub Cmd_ActualsQuery_Click()
On Error GoTo Err_Cmd_ActualsQuery_Click
 
 
 
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
 
 
 
''Constant Select statement for the Query definition
strSQL = "SELECT Tbl_Actuals.* " & _
"FROM Tbl_Actuals"
 
strWhere = "WHERE"
 
strOrder = "ORDER BY tbl_Actuals.UCMG_Program_Code;"
 
 
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.cmb_ActualsYear) Then '<--If the textbox txt_ActualsYear contains no data THEN do nothing
strWhere = strWhere & " (qry_Actuals.Actuals_Year) Like '*" & cmb_ActualsYear & "*'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
 
If Not IsNull(Me.Cmb_Type) Then
strWhere = strWhere & " (qry_Actuals.PS_OVF) Like '*" & Me.Cmb_Type & "*'  AND"
End If
 
If Not IsNull(Me.Cmb_Period) Then
strWhere = strWhere & " (qry_Actuals.Period) Like '*" & Me.Cmb_Period & "*'  AND"
End If
 
 
'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
 
 
Exit_Cmd_ActualsQuery_Click:
    Exit Sub
 
Err_Cmd_ActualsQuery_Click:
    MsgBox Err.Description
    Resume Exit_Cmd_ActualsQuery_Click
    
End Sub
 
''Pass the SQL to the RecordSource of the sfrms
 
'Me.sfrm_Actuals_OVF.Form.RecordSource = strSQL & " " & strWhere & "AND((Tbl_Actuals.PS_OVF)=""OVF"")  " & strOrder
 
'====================================================
'  requires:
'    reference(s) to:
'       Micrsoft DAO 3.6 Object Library
'====================================================
Public Function CreateQuery()
    Dim qdfNew As DAO.QueryDef
' turn off error handling
On Error Resume Next
    With CurrentDb
        ' delete any temp query
        .QueryDefs.Delete "zsqryTemp"
        DoEvents
        ' create a new temp query
        Set qdfNew = .CreateQueryDef("zsqryTemp")
        DoEvents
        ' set the sql of the new query
        qdfNew.SQL = strSQL & " " & strWhere & " " & strOrder
        DoEvents
        DoCmd.OpenQuery "zsqryTemp", acViewNormal, acEdit
        DoEvents
        '=== delete the temp query
        '===.QueryDefs.Delete "zsqryTemp"
        DoEvents
        .Close
    End With
    DoEvents
' turn error handling back on
On Error GoTo Err_Cmd_ActualsQuery_Click
End Function

Open in new window

Commented:
did you try adding the code that i posted last? the code i originally posted changed the sql in the query but did not open it.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Works great!! Thanks everyone for all of your help!
You are welcome.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.