Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

OPENQUERY Where clause?

Posted on 2009-05-08
10
Medium Priority
?
1,573 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

783 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