Solved

OPENQUERY Where clause?

Posted on 2009-05-08
10
1,490 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

12 Experts available now in Live!

Get 1:1 Help Now