• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 54
  • Last Modified:

MS Acces Query Where Clause on Fly with condition

I am creating on fly  “Where condition for a query .
I am using PatHartman  example:
*****************************************
Private Sub cmdViewResults_Click()
   On Error GoTo Err_Proc
    Call BuildSQL
    DoCmd.OpenForm "frmSearchResults", , , , , , Me.name
    Me.Visible = False

Exit_Proc:
   On Error GoTo 0
   Exit Sub

Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdViewResults_Click of VBA Document Form_frmClientSearch"
    End Select
End Sub

Public Sub BuildSQL()
Dim strSQL As String
Dim strSelect As String
Dim strSelectForExport As String
Dim strWHERE As String
Dim strCondition As String
Dim strOrderBy As String

    strSelect = "Select * from qClientList "
    strSelectForExport = "Select * from qClientListExport "
    strOrderBy = " ORDER BY FullName"
    strWHERE = ""
   
    If Me.txtFirstName & "" = "" Then
    Else
        strWHERE = "FirstName Like " & QUOTE & Me.txtFirstName & "*" & QUOTE
    End If
    If Me.txtLastName & "" = "" Then
    Else
        strCondition = "LastName Like " & QUOTE & Me.txtLastName & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtcity & "" = "" Then
    Else
        strCondition = "City Like " & QUOTE & Me.txtcity & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtaddress & "" = "" Then
    Else
        strCondition = "Address Like " & QUOTE & Me.txtaddress & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkBUP = True Then
        strCondition = "BUP = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.cboCareMgrID & "" = "" Then
    Else
        strCondition = "CareMgrID = " & Me.cboCareMgrID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.cboGenderID & "" = "" Then
    Else
        strCondition = "GenderID = " & Me.cboGenderID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtZip & "" = "" Then
    Else
        strCondition = "Zip = '" & Me.txtZip & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If

    If Me.cboRaceID & "" = "" Then
    Else
        strCondition = "RaceID = " & Me.cboRaceID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.cboDiagnosis & "" = "" Then
    Else
        strCondition = "Diagnosis = '" & Me.cboDiagnosis & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtpid & "" <> "" Then
        strCondition = "PID = '" & Me.txtpid & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtClientID & "" <> "" Then
        strCondition = "ClientID = " & Me.txtClientID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtems & "" <> "" Then
        strCondition = "EMS = '" & Me.txtems & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
       
    If Me.txtPhone & "" <> "" Then
        strCondition = "(Phone = '" & Me.txtPhone & "' OR CellPhone = '" & Me.txtPhone & "')"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
   
    If Me.cboCounty & "" <> "" Then
        strCondition = "County = '" & Me.cboCounty & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
       
    If Me.cboLevelID & "" <> "" Then
        strCondition = "LevelID = " & Me.cboLevelID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
   
    If Me.cboTier & "" <> "" Then
        strCondition = "PCATier = " & Me.cboTier
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If

    If Me.cboProgramID & "" = "" Then
    Else
        strCondition = "ProgramID = " & Me.cboProgramID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
   
    'Funding
    If Me.chkT19 = True Then
        strCondition = "T19 = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If

    If Me.chkSelf = True Then
        strCondition = "Self = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkMFP = True Then
        strCondition = "MFP = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
   
    If Me.chkNOTT19 = True Then
        strCondition = "Not(T19 = True)"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkNOTSelf = True Then
        strCondition = "Not (Self = True)"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkNOTMFP = True Then
        strCondition = "Not(MFP = True)"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If

    Select Case Me.fraStatus
        Case 1
            strCondition = "StatusID = 1318"          'Open
        Case 2
            strCondition = "StatusID = 1319"          'Closed
        Case 4
            strCondition = "StatusID = 1427"          'Pending
        Case Else
            strCondition = ""
    End Select
    If strWHERE = "" Then
        If strCondition = "" Then
        Else
            strWHERE = strCondition
        End If
    Else
        If strCondition = "" Then
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
   
   
'query for form/report
    If strWHERE = "" Then
        strSQL = strSelect
    Else
        strSQL = strSelect & " WHERE " & strWHERE
    End If

    strSQL = strSQL & strOrderBy
    Me.txtQuery = strSQL
   
'query for Excel
    If strWHERE = "" Then
        strSQL = strSelectForExport
    Else
        strSQL = strSelectForExport & " WHERE " & strWHERE
    End If
   
    strSQL = strSQL & strOrderBy
    Me.txtQueryExport = strSQL
End Sub
***********************************************************



I have a question I have another query e.g.  “Qry_Exception_Race” that have some exception records in.
I used this query in strCondition part below.
But execution is very slow I was wondering can excecute that query before strCodnition
I am not sure is this possible, I need something as this.
…….
If Me.cboRaceID & "" = "" Then
   Else
       strCondition = " Not RaceID  In  (Select Qry_Exception_Race.Race_ID from Qry_Exception_Race)

        If strWHERE = "" Then
            strWHERE = strCondition
        Else
           strWHERE = strWHERE & " AND " & strCondition
        End If
   End If
……………

But execution is very slow I was wondering can execute that query “Qry_Exception_Race” before or create query def before  and create some recordset with Race_ID  and use it in  strCondition  to get on speed something like:
…….
If Me.cboRaceID & "" = "" Then
   Else
       strCondition = " Not RaceID  In  (rstRace.Race_ID)

        If strWHERE = "" Then
            strWHERE = strCondition
        Else
           strWHERE = strWHERE & " AND " & strCondition
        End If
   End If
……………
Any suggestion?
0
Taras
Asked:
Taras
  • 3
  • 2
  • 2
  • +1
1 Solution
 
BitsqueezerCommented:
Hi,

you should make sure that you set an index on the ID column of both tables. Moreover you should always try to avoid the "IN" clause and use "EXISTS" instead. The reason is that with "IN" all the records are created in a result table and then it is compared, with "EXISTS" only the existance of the record is checked and no result table (the one in the brackets) will be needed. So use instead:

Not Exists (Select 1 FROM from Qry_Exception_Race WHERE Qry_Exception_Race.Race_ID = RaceID) 

Open in new window


In most cases you can always use Exists instead of In.

Cheers,

Christian
0
 
TarasAuthor Commented:
Somehow this Not Exists  shows nothing - no records,  on another spot where I need to use Exists it shows me wrong number of records.???
ID column is Indexed.
Not sure why this happening
0
 
BitsqueezerCommented:
Hi,

you should add the table alias of the main query to RaceID, I did not knew it from your query. In your original it is named only "RaceID" and as the same RaceID is also in the inner query it may select the wrong field. So if the main query uses an alias i.e. "X" it would then be "...WHERE Qry_Exception_Race.Race_ID = X.RaceID".

Cheers,

Christian
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
TarasAuthor Commented:
Yes I got it but speed is the same slow even more slow then when I used Not IN ????
0
 
BitsqueezerCommented:
Hi,

it would be helpful if you could use the Immediate window of VBA to print the complete SQL string and copy it here. Also you should copy the SQL text of this "Qry_Exception_Race" because this could also be a performance bottleneck.

Moreover to analyze the reason you should check all the fields you use in your VBA if they have indexes. As you use LIKE but only with an asterisk at the end they would be able to use an index (not possible if you would add an asterisk at the beginning of the search string).

Best thing would be if you could upload a demo database with some demo records. If this one is connected to a database server as backend the performance could also be a problem of the way you query it. The asterisk says that it is an Access query, if you would have i.e. SQL Server as backend the joker would be "%" instead. If you have a database server in the backend then you should assemble the WHERE string as filter string and use it on the server to assemble the complete SQL String there and execute it there, then the performance would be very much better.

Cheers,

Christian
0
 
aikimarkCommented:
1. Left join the main table with the exception table (Qry_Exception_Race) in your FROM clause
2. In the RaceID part of the WHERE clause construction, change the  code to:
If Me.cboRaceID & "" = "" Then
   Else
       strCondition = " Qry_Exception_Race.RaceID  Is Null " 

        If strWHERE = "" Then
            strWHERE = strCondition
        Else
           strWHERE = strWHERE & " AND " & strCondition
        End If
   End If

Open in new window

0
 
aikimarkCommented:
optimal solution provided
0

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now