Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

OPENQUERY Where clause?

Posted on 2009-05-08
10
Medium Priority
?
1,553 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

722 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