Solved

OPENQUERY Where clause?

Posted on 2009-05-08
10
1,521 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

0
Comment
Question by:SueMos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 13

Expert Comment

by:adraughn
ID: 24336579
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
 
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24336620
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

0
 
LVL 13

Expert Comment

by:adraughn
ID: 24336674
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SueMos
ID: 24337045
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

0
 
LVL 13

Expert Comment

by:adraughn
ID: 24337080
before the end sub just add the code to open your query.

Docmd.openquery "queryname"
0
 

Author Comment

by:SueMos
ID: 24337114
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

0
 
LVL 13

Expert Comment

by:adraughn
ID: 24337134
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.
0
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 500 total points
ID: 24337238
You need to put just the code within the function (seen snippet) inside your Private Sub Cmd_ActualsQuery_Click.
    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

Open in new window

0
 

Author Closing Comment

by:SueMos
ID: 31579462
Works great!! Thanks everyone for all of your help!
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24338112
You are welcome.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question