Solved

OPENQUERY Where clause?

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

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now