MS Access Query Creating Where Clause on fly with conditions

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 want to use this query in strCondition part
I am not sure is this possible, I need something as this.
…….
If Me.cboRaceID & "" = "" Then
   Else
       strCondition = "RaceID <> (Select Qry_Exception_Race.Race_ID from Qry_Exception_Race)

        If strWHERE = "" Then
            strWHERE = strCondition
        Else
           strWHERE = strWHERE & " AND " & strCondition
        End If
   End If
……………
TarasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
First of all: Embed code into the [code][/code] tags (the CODE button).

Then, when I understand you correctly, you want to test this:
strCondition = "RaceID <> (Select Qry_Exception_Race.Race_ID from Qry_Exception_Race)
?
In this case you need an IN predicate:

strCondition = "NOT RaceID IN (SELECT Race_ID FROM Qry_Exception_Race)"

Open in new window

An EXISTS predicate is also possible, but this requires more effort, cause you need table alias names.

These constructs

If Me.txtFirstName & "" = "" Then
Else
  strWHERE = "FirstName Like " & QUOTE & Me.txtFirstName & "*" & QUOTE
End If

Open in new window

are wrong. You need to escape potential quotes in the content:

If Me.txtFirstName & "" = "" Then
Else
  strWHERE = "FirstName Like " & QUOTE & Replace(Me.txtFirstName & "*", QUOTE, QUOTE & QUOTE)  & QUOTE
End If

Open in new window

This is better done in a function and use positive IF conditions, when possible:

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
    strWHERE = "FirstName LIKE " SqlQuote(Me.txtFirstName & "*")
  End If

End Sub
 
'Copy to a standard module.
Public Function SqlQuote(AString As String, Optional ADelimiter As String = "'") As String

  SqlQuote = ADelimiter & Replace(AString, ADelimiter, ADelimiter & ADelimiter) & ADelimiter

End Function

Open in new window

And as this is a reoccurring pattern, you should further shorten

  If Me.txtFirstName & "" <> "" Then  
    strWHERE = strWHERE & "AND FirstName LIKE " & SqlQuote(Me.txtFirstName & "*")
  End If
  
  If Me.txtLastName & "" <> "" Then    
     strWHERE = strWHERE & " AND LastName LIKE " & SqlQuote(Me.txtLastName & "*")
  End If
  
  If Me.txtcity & "" <> "" Then  
    strWHERE = strWHERE & " AND City LIKE " & SqlQuote(Me.txtcity & "*")
  End If

Open in new window

like this:

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 = ""
  
  BuildCondition "FirstName", Me.txtFirstName, strWhere
  BuildCondition "LastName", Me.txtLastName, strWhere
  BuildCondition "City", Me.txtcity, strWhere
  
End Sub
 
Private Sub BuildCondition(AColumnName As String, AValue As String, ByRef ACondition As String) 

  If AValue & "" <> "" Then  
    ACondition = ACondition & "AND [" & AColumnName & "] LIKE " & SqlQuote(AValue & "*")
  End If  

End Function 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
ste5an


strCondition = "NOT RaceID IN (SELECT Race_ID FROM Qry_Exception_Race)"

For this I am getting Run-time error 13
Type Mismatch?
0
TarasAuthor Commented:
Thank you very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.