Build the 'where' sql statement following search form selection (1 Viewer)

C

Chaga

Member
Local time
Today, 20:01
Joined
Aug 19, 2022
Messages
34
Hi everyone,
I am building a search form for my DB, and added 5 checkboxes, with comboboxes/textboxes next to them to let the user make a search selection.
They are cbPosition, cbDepartment, cbInterviewer, cbFeedback, cbRating and cboPosition ,cboDepartment ,cboInterviewer ,txtFeedback, cboRating.
So I need to loop through the checkboxes and if they are ticked, append the value of the combo or textbox next to it to the 'where' statement of the SQL string.
Any help is greatly appreciated.
Thank you
 
C

Chaga

Member
Local time
Today, 20:01
Joined
Aug 19, 2022
Messages
34
Code: 
Private Sub cmdPerson_Click()
On Error GoTo errHandler

Dim msg$, title$
Dim cptn$
Dim SQL$, where$
Dim fn%, ln%
title$ = "PEOPLE SEARCH"

' Commence construction of SELECT statement for list box's RowSource SQL

    SQL$ = _
    "SELECT ID, " & _
    "[Last Name] & chr(44) & chr(32) &[First Name] " & _
    "AS myPerson " & _
    "FROM [qry2] "

' Commence construction of SQL WHERE statement

    With Me
        If .cbLastName Then
        ' User chooses to search for Last Name
        ' Check that user has input Last Name search string
            If IsNull(.tebLastName) Then
                msg$ = "Missing last name search string"
                MsgBox msg$, vbExclamation, title$
                .tebLastName.SetFocus
                With .lbSearchResults
                    .RowSource = vbNullString
                    .Requery
                End With
                Exit Sub
        Else
            ln% = 1
            End If
        End If
        If .cbFirstName Then
            If IsNull(.tebFirstName) Then
            ' User chooses to search for First Name
            ' Check that user has input First Name search string
                msg$ = "Missing first name search string"
                MsgBox msg$, vbExclamation, title$
                .tebFirstName.SetFocus
        With .lbSearchResults
            .RowSource = vbNullString
            .Requery
        End With
        Exit Sub
        Else
            fn% = 2
        End If
    End If
        ' Continue construction of WHERE condition to include AND/OR logic
        Select Case ln% + fn%
            Case 1    'Lastname only - no logic component
                cptn$ = "Results for Person's Last Name"
                where$ = "[Last Name] Like '*" & .tebLastName & "*'"
            Case 2    'Firstname only - no logic component
                cptn$ = "Results for Person's First Name"
                where$ = "[First Name] Like '*" & .tebFirstName & "*'"
            Case 3    'Both Lastname and Firstname; apply logic
                cptn$ = "Results for Person's Full Name"
            ' Determine logic and assimilate to complete WHERE condition
      Select Case .FraPersonLogic
        Case 1 'AND
            where$ = _
            "[Last Name] Like '*" & .tebLastName & "*'" & _
            " AND " & _
            "[First Name] Like '*" & .tebFirstName & "*'"
        Case 2 'OR
            where$ = _
            "[Last Name] Like '*" & .tebLastName & "*'" & _
            " OR " & _
            "[First Name] Like '*" & .tebFirstName & "*'"
        End Select
        Case Else
            msg$ = "Please make an appropriate choice of names"
            MsgBox msg$, vbExclamation, title$
            Exit Sub
        End Select

        ' Complete SQL
        SQL$ = SQL$ & " WHERE " & where$ & _
        " GROUP BY ID, [Last Name] & Chr(44) & Chr(32) & [First Name], [Last Name], [First Name] ORDER BY [Last Name], [First Name];"
        ' Update list

        With .lbSearchResults
            .RowSource = SQL$
            .Requery
        End With

        ' Update list box caption
            .lblResults.Caption = cptn$
            .Text35.Value = SQL$
        End With
        
procDone:
        Exit Sub

errHandler:
    msg$ = "Error performing search for people"
    MsgBox msg$, vbExclamation, title$
Resume procDone

End Sub

Above is some code I found online and used for the other tables, and it was easy since there were only 2 parameters, but with 5, I am having trouble applying the same.
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 18:01
Joined
Jul 21, 2014
Messages
2,288
The above code looks a lot more complicated than it needs to be.

Can you post a screenshot of your form - I'm just trying to get an idea of the controls involved.

Are the checkboxes part of an Option Group, or do they operate independently?
 
C

Chaga

Member
Local time
Today, 20:01
Joined
Aug 19, 2022
Messages
34
cheekybuddha said:
The above code looks a lot more complicated than it needs to be.

Can you post a screenshot of your form - I'm just trying to get an idea of the controls involved.

Are the checkboxes part of an Option Group, or do they operate independently?
Click to expand...
search form.png


No they are not part of an option group, the 2 boxes on the left each search in a different table, and I need to do the same for the 3rd one.
List box on the right displays results, and textbox below is only temporary showing me the constructed sql statement just to double check.
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 18:01
Joined
Jul 21, 2014
Messages
2,288
Do you need the checkboxes?

Wouldn't it be enough just to have values in the respective textboxes to determine whether the field should be searched?

What are the other empty combos?
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 18:01
Joined
Jul 21, 2014
Messages
2,288
Also, what are the names of the And/Or option frames in each box and what are the values of the options?
 
C

Chaga

Member
Local time
Today, 20:01
Joined
Aug 19, 2022
Messages
34
cheekybuddha said:
Do you need the checkboxes?

Wouldn't it be enough just to have values in the respective textboxes to determine whether the field should be searched?

What are the other empty combos?
Click to expand...
I kept the checkboxes mostly for consistency to be similar to the other 2, but if it unnecessarily complicates things, I can do without.
The combos contain lookup values like position, interviewer, rating etc.. the only textbox is for a feedback field.

search2.png
 
C

Chaga

Member
Local time
Today, 20:01
Joined
Aug 19, 2022
Messages
34
cheekybuddha said:
Also, what are the names of the And/Or option frames in each box and what are the values of the options?
Click to expand...
i won't use the AND and OR for that last one as it will make things way too complicated. So it's only gonna be AND
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 18:01
Joined
Jul 21, 2014
Messages
2,288
I would lose all the checkboxes (horrible user experience anyway!)

Then try something along the lines of:
Code: 
' Commence construction of SELECT statement for list box's RowSource SQL
  SQL$ = _
    "SELECT ID, " & _
    "[Last Name] & chr(44) & chr(32) &[First Name] " & _
    "AS myPerson " & _
    "FROM [qry2] "

' Commence construction of SQL WHERE statement
  With Me
    Select Case True
    Case Len(.tebFirstName & vbNullString) > 0 And Len(.tebLastName & vbNullString) > 0
      where$ = "([First Name] LIKE '*" & .tebFirstName & "*'" & _
              IIf(.FraPersonLogic = 1, " AND ", " OR ") & _
              "[Last Name] LIKE '*" & .tebLastName & "*')"
    Case Len(.tebFirstName & vbNullString) > 0
      where$ = "([First Name] LIKE '*" & .tebFirstName & "*'"
    Case Len(.tebLastName & vbNullString) > 0
      where$ = "([Last Name] LIKE '*" & .tebLastName & "*'"
    End Select
    If Len(.tebPrevCompany & vbNullString) > 0 Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "PrevCompany LIKE '*" & .tebPrevCompany & "*'"
    End If
    If Len(.tebPrevPosition & vbNullString) > 0 Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "PrevPosition LIKE '*" & .tebPrevPosition & "*'"
    End If
    If Not IsNull(.cobPosition) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Position = " & .cobPosition
    End If
    If Not IsNull(.cobInterviewer) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Interviewer = " & .cobInterviewer
    End If
    If Not IsNull(.cobRating) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Rating = " & .cobRating
    End If
    If Len(.tebFeedback & vbNullString) > 0 Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Feedback LIKE '*" & .tebFeedback & "*'"
    End If
  ' Complete SQL
    SQL$ = SQL$ & IIf(Len(where$), " WHERE " & where$ , vbNullString)  & _
          " GROUP BY ID, [Last Name] & Chr(44) & Chr(32) & [First Name], [Last Name], [First Name] ORDER BY [Last Name], [First Name];"

    With .lbSearchResults
      .RowSource = SQL$               ' No need to requery when you set the RowSource
    End With
' ...

  End With

The above assumes that the comboboxes have two columns bound to a numeric id from a lookup table. If you are using single column text values (you probably shouldn't!) then you will need to adjust with single quotes around the values.

I have made guesses at the field and control names that you haven't shared already, so check that they are what you have in your tables/query/form.
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 18:01
Joined
Jul 21, 2014
Messages
2,288
Also, do you really need the GROUP BY clause? What's the SQL of qry2 ?
 
C

Chaga

Member
Local time
Today, 20:01
Joined
Aug 19, 2022
Messages
34
cheekybuddha said:
I would lose all the checkboxes (horrible user experience anyway!)

Then try something along the lines of:
Code: 
' Commence construction of SELECT statement for list box's RowSource SQL
  SQL$ = _
    "SELECT ID, " & _
    "[Last Name] & chr(44) & chr(32) &[First Name] " & _
    "AS myPerson " & _
    "FROM [qry2] "

' Commence construction of SQL WHERE statement
  With Me
    Select Case True
    Case Len(.tebFirstName & vbNullString) > 0 And Len(.tebLastName & vbNullString) > 0
      where$ = "([First Name] LIKE '*" & .tebFirstName & "*'" & _
              IIf(.FraPersonLogic = 1, " AND ", " OR ") & _
              "[Last Name] LIKE '*" & .tebLastName & "*')"
    Case Len(.tebFirstName & vbNullString) > 0
      where$ = "([First Name] LIKE '*" & .tebFirstName & "*'"
    Case Len(.tebLastName & vbNullString) > 0
      where$ = "([Last Name] LIKE '*" & .tebLastName & "*'"
    End Select
    If Len(.tebPrevCompany & vbNullString) > 0 Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "PrevCompany LIKE '*" & .tebPrevCompany & "*'"
    End If
    If Len(.tebPrevPosition & vbNullString) > 0 Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "PrevPosition LIKE '*" & .tebPrevPosition & "*'"
    End If
    If Not IsNull(.cobPosition) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Position = " & .cobPosition
    End If
    If Not IsNull(.cobInterviewer) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Interviewer = " & .cobInterviewer
    End If
    If Not IsNull(.cobRating) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Rating = " & .cobRating
    End If
    If Len(.tebFeedback & vbNullString) > 0 Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Feedback LIKE '*" & .tebFeedback & "*'"
    End If
  ' Complete SQL
    SQL$ = SQL$ & IIf(Len(where$), " WHERE " & where$ , vbNullString)  & _
          " GROUP BY ID, [Last Name] & Chr(44) & Chr(32) & [First Name], [Last Name], [First Name] ORDER BY [Last Name], [First Name];"

    With .lbSearchResults
      .RowSource = SQL$               ' No need to requery when you set the RowSource
    End With
' ...

  End With

The above assumes that the comboboxes have two columns bound to a numeric id from a lookup table. If you are using single column text values (you probably shouldn't!) then you will need to adjust with single quotes around the values.

I have made guesses at the field and control names that you haven't shared already, so check that they are what you have in your tables/query/form.
Click to expand...
Yes the combos have 2 bound columns (with ID hidden).
I take it you merged all of them into a single search button?
Will give it a try and let you know. Much appreciated.
 
C

Chaga

Member
Local time
Today, 20:01
Joined
Aug 19, 2022
Messages
34
cheekybuddha said:
Also, do you really need the GROUP BY clause? What's the SQL of qry2 ?
Click to expand...
Unfortunately yes, as the job history and interview tables both have a one to many relationship for positions, so I am getting repeated names in the result.
 
C

Chaga

Member
Local time
Today, 20:01
Joined
Aug 19, 2022
Messages
34
Code: 
  With Me

    If Not IsNull(.cboPosition) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Position = " & .cboPosition
    End If
    If Not IsNull(.cboSecPos) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "SecPosition like '" & .cboSecPos & "'"
    End If
    If Not IsNull(.cboInterviewer) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "InterviewerID = " & .cboInterviewer
    End If
    If Not IsNull(.cboRating) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Ratingdesc >= " & .cboRating.Column(1)
    End If
    If Len(.tebFeedback & vbNullString) > 0 Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Feedback LIKE '*" & .tebFeedback & "*'"
    End If
  ' Complete SQL
    SQL$ = SQL$ & IIf(Len(where$), " WHERE " & where$, vbNullString) & _
          " GROUP BY ID, [Last Name] & Chr(44) & Chr(32) & [First Name], [Last Name], [First Name] ORDER BY [Last Name], [First Name];"

So that's the code I ended up using and it worked like a charm.
Changed cboSecPos as it reads from a simple value list and not a table, and for the rating had to modify it to be able to use >=
I will try and use the single search button once am done with all the tables that need searching.
Can't thank you enough mate.
 
C

Chaga

Member
Local time
Today, 20:01
Joined
Aug 19, 2022
Messages
34
cheekybuddha said:
I would lose all the checkboxes (horrible user experience anyway!)

Then try something along the lines of:
Code: 
' Commence construction of SELECT statement for list box's RowSource SQL
  SQL$ = _
    "SELECT ID, " & _
    "[Last Name] & chr(44) & chr(32) &[First Name] " & _
    "AS myPerson " & _
    "FROM [qry2] "

' Commence construction of SQL WHERE statement
  With Me
    Select Case True
    Case Len(.tebFirstName & vbNullString) > 0 And Len(.tebLastName & vbNullString) > 0
      where$ = "([First Name] LIKE '*" & .tebFirstName & "*'" & _
              IIf(.FraPersonLogic = 1, " AND ", " OR ") & _
              "[Last Name] LIKE '*" & .tebLastName & "*')"
    Case Len(.tebFirstName & vbNullString) > 0
      where$ = "([First Name] LIKE '*" & .tebFirstName & "*'"
    Case Len(.tebLastName & vbNullString) > 0
      where$ = "([Last Name] LIKE '*" & .tebLastName & "*'"
    End Select
    If Len(.tebPrevCompany & vbNullString) > 0 Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "PrevCompany LIKE '*" & .tebPrevCompany & "*'"
    End If
    If Len(.tebPrevPosition & vbNullString) > 0 Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "PrevPosition LIKE '*" & .tebPrevPosition & "*'"
    End If
    If Not IsNull(.cobPosition) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Position = " & .cobPosition
    End If
    If Not IsNull(.cobInterviewer) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Interviewer = " & .cobInterviewer
    End If
    If Not IsNull(.cobRating) Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Rating = " & .cobRating
    End If
    If Len(.tebFeedback & vbNullString) > 0 Then
      where$ = where$ & IIf(Len(where$), " AND ", vbNullString) & "Feedback LIKE '*" & .tebFeedback & "*'"
    End If
  ' Complete SQL
    SQL$ = SQL$ & IIf(Len(where$), " WHERE " & where$ , vbNullString)  & _
          " GROUP BY ID, [Last Name] & Chr(44) & Chr(32) & [First Name], [Last Name], [First Name] ORDER BY [Last Name], [First Name];"

    With .lbSearchResults
      .RowSource = SQL$               ' No need to requery when you set the RowSource
    End With
' ...

  End With

The above assumes that the comboboxes have two columns bound to a numeric id from a lookup table. If you are using single column text values (you probably shouldn't!) then you will need to adjust with single quotes around the values.

I have made guesses at the field and control names that you haven't shared already, so check that they are what you have in your tables/query/form.
Click to expand...
Don't know if i could bother you with one last related thing.
The search form is opened from another one "Contact List" which is continuous form displaying all people.
I want to add a button to close the search form and filter my contact list form (using ID) from the search result.
Could you help me out with this one?
Thanks again.
 
C

Chaga

Member
Local time
Today, 20:01
Joined
Aug 19, 2022
Messages
34
cheekybuddha said:
Is lbSearchResults single-select or multi-select?
Click to expand...
Single, I am using it just for a preview of the search results.
Users will eventually use the contact list form to access and edit the results.
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 18:01
Joined
Jul 21, 2014
Messages
2,288
In your search form's close event (or in a Close button) you can have some code like:
Code: 
If Not IsNull(Me.lbSearchResults) And CurrentProject.AllForms("Contact List").IsLoaded Then
  With Forms.[Contact List]
    .RecordsetClone.FindFirst "ContactID = " & Me.lbSearchResults
    If Not .NoMatch Then
      .Bookmark = .RecordsetClone.Bookmark
    End If
  End With
End If
' Uncomment below if this is run from a Close button
' DoCmd.Close acForm, Me.Name
(NB Untested aircode)
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 18:01
Joined
Jul 21, 2014
Messages
2,288
Chaga said:
close the search form and filter my contact list form (using ID)
Click to expand...
Oops! I mis-read your reuirement.

Adjust to:
Code: 
If Not IsNull(Me.lbSearchResults) And CurrentProject.AllForms("Contact List").IsLoaded Then
  With Forms.[Contact List]
    .Filter = "ID = " & Me.lbSearchResults
    .FilterOn = True
  End With
End If
' Uncomment below if this is run from a Close button
' DoCmd.Close acForm, Me.Name
 
C

Chaga

Member
Local time
Today, 20:01
Joined
Aug 19, 2022
Messages
34
cheekybuddha said:
Oops! I mis-read your reuirement.

Adjust to:
Code: 
If Not IsNull(Me.lbSearchResults) And CurrentProject.AllForms("Contact List").IsLoaded Then
  With Forms.[Contact List]
    .Filter = "ID = " & Me.lbSearchResults
    .FilterOn = True
  End With
End If
' Uncomment below if this is run from a Close button
' DoCmd.Close acForm, Me.Name
Click to expand...
Yeah no worries, but tested it, and it's not filtering for all the results if no selection made.
If I select one of them in the listbox, filtering works fine.
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 18:01
Joined
Jul 21, 2014
Messages
2,288
So, adjust it to remove filter if no selection is made:
Code: 
If CurrentProject.AllForms("Contact List").IsLoaded Then
  With Forms.[Contact List]
    If Not IsNull(Me.lbSearchResults) Then
      .Filter = "ID = " & Me.lbSearchResults
      .FilterOn = True
    Else
      .FilterOn = False
    End If
  End With
End If
' Uncomment below if this is run from a Close button
' DoCmd.Close acForm, Me.Name
 
You must log in or register to reply here.

Similar threads

B
Using date from a text box in a where condition
Replies
6
Views
377
Pat Hartman
P
E
Solved Listbox search not working with aggregated query
Replies
7
Views
285
elannesse
E
M
Add row number to randomly selected records
Replies
17
Views
643
LarryE
LarryE
amorosik
SUM function inside textbox on the bottom of the form return #Error
Replies
8
Views
224
amorosik
amorosik
T
Find works differently for combobox with value list
Replies
6
Views
237
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom