Solved

OPENQUERY Where clause?

Posted on 2009-05-08
10
1,500 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

770 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