Filter Form with multiple Unbound combo box and dates (1 Viewer)

[theinviter]

Dears;
I need your support, as I have a form with multiple un-bound combo-box and need to filter the form based on the user selection. as i tried below but not work Properly.
and will not filer the form.
so can you guide me how to solve this, File attached.

Private Sub filterThisForm2()
On Error GoTo errhandler:
n = 1 / 0 ' cause an error
Dim strFilter As String

If Len(Me!Combo_Clinic & "") <> 0 Then
strFilter = "[Location Code] Like """ & Me!Combo_Clinic & """ And "
End If
If Len(Me!Combo_Machine & "") <> 0 Then
strFilter = strFilter & "[Machine Name] Like ""*" & Me!Combo_Machine & "*"" And "
End If
If Len(Me!Combo_Brand & "") <> 0 Then
strFilter = "[Brand] Like ""*" & Me!Combo_Brand & "*"" And "
End If

If Len(Me!Combo_Status & "") <> 0 Then
strFilter = "[Repair Status] Like """ & Me!Combo_Status & """ And "
End If

If Len(Me!Date_From & "") <> 0 Then
strFilter = "[Date of Occurrence] >= #" & Format(Nz(Me.Date_From, 1), "yyyy\/mm\/dd") & "# AND [Date of Occurrence] <= #" & Format(Nz(Me.Date_To, 2958465), "yyyy\/mm\/dd") & "# And "
End If

If Len(strFilter) <> 5 Then
strFilter = Left$(strFilter, Len(strFilter) - 5)
With Me
.Filter = strFilter
.FilterOn = True
End With
Else
Me.FilterOn = False
End If

Exit Sub
errhandler:
' error handling code
Resume Next
End Sub

 

[Josef P.]

Some statements are missing strFilter &

   If Len(Me!Combo_Machine & "") <> 0 Then
       strFilter = strFilter & "[Machine Name] Like ""*" & Me!Combo_Machine & "*"" And "
   End If
   If Len(Me!Combo_Brand & "") <> 0 Then
       strFilter =  "[Brand] Like ""*" & Me!Combo_Brand & "*"" And "
   End If
...

=>

   If Len(Me!Combo_Machine & "") <> 0 Then
       strFilter = strFilter & "[Machine Name] Like ""*" & Me!Combo_Machine & "*"" And "
   End If
   If Len(Me!Combo_Brand & "") <> 0 Then
       strFilter =  strFilter & "[Brand] Like ""*" & Me!Combo_Brand & "*"" And "
   End If
...

Note:
n = 1 / 0 ' cause an error
I would skip that

Attached is your example file with a class for filtering. Your code would look like this:

Private Sub filterThisForm2()

    Dim strFilter As String

    With New FilterStringBuilder
        .ConfigSqlFormat "\#yyyy-mm-dd\#", "True", "*"
   
        .Add "[Location Code]", SQL_Text, SQL_Like + SQL_Add_WildCardPrefix + SQL_Add_WildCardSuffix, Me.Combo_Clinic.Value
        .Add "[Machine Name]", SQL_Text, SQL_Like + SQL_Add_WildCardPrefix + SQL_Add_WildCardSuffix, Me.Combo_Machine.Value
        .Add "Brand", SQL_Text, SQL_Like + SQL_Add_WildCardPrefix + SQL_Add_WildCardSuffix, Me.Combo_Brand.Value
        .Add "[Repair Status]", SQL_Text, SQL_Like + SQL_Add_WildCardPrefix + SQL_Add_WildCardSuffix, Me.Combo_Status.Value
        .Add "[Date of Occurrence]", SQL_Date, SQL_Between, Me.Date_From.Value, Me.Date_To
       
        strFilter = .ToString(SQL_And)
    End With

    If Len(strFilter) > 0 Then
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If

End Sub

 

[MajP]

I use the modules from here

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

Which lets me do the whole thing with this much code

Private Function FilterForm()
  Dim Clinic As String
  Dim Brand As String
  Dim Machine As String
  Dim Status As String
  Dim fltr As String
  Dim btwn As String
 
  Clinic = GetFilterFromControl(Me.Combo_Clinic)
  Brand = GetFilterFromControl(Me.Combo_Brand)
  Machine = GetFilterFromControl(Me.Combo_Machine)
  Status = GetFilterFromControl(Me.Combo_Status)
 
  If IsDate(Me.Date_From) And IsDate(Me.Date_To) Then
    btwn = GetBetweenFilter(Me.Date_From, Me.Date_To, "[Date of Occurrence]")
  End If
  fltr = CombineFilters(ct_And, Clinic, Brand, Machine, Status, btwn)
 
  If fltr <> "" Then
    Me.Filter = fltr
    Me.FilterOn = True
  Else
    Me.Filter = ""
    Me.FilterOn = False
  End If
 
End Function

 

[theinviter]

thanks all,
but is there a way to extract the data with applied filter.

thanks

 

[MajP]

but is there a way to extract the data with applied filter.

Not sure what you mean, but you can apply the filter to a query or create a recordset from it.

 

[theinviter]

Not sure what you mean, but you can apply the filter to a query or create a recordset from it.

after applying the filter enable to extract the filtered data into excel file. by clicking on a button

 

[MajP]

Create a query called "ExportQuery"
add a button and code

Private Sub cmdExport_Click()
 ExportToExcel
End Sub
Private Sub ExportToExcel()
  Dim strSql As String
  Dim qdf As QueryDef
  If Me.Filter <> "" Then
    strSql = "Select * from [Tracker Query] WHERE " & Me.Filter
    Set qdf = CurrentDb.QueryDefs("exportQuery")
    qdf.sql = strSql
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qdf.Name, CurrentProject.Path & "\Export.xlsx"
  Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "[tracker query]"
  End If
  MsgBox "Check Local Folder"
End Sub

 

[Pat Hartman]

You seem to have a misconception regarding combos. In a textbox, you type some number of characters but in a combo, when you click, you are selecting an ENTIRE value, not a partial value so you would NEVER use LIKE if your criteria is coming from a combo. Also, Combo are not bound to the string you are seeing, the string you see is coming from the RowSource. The value that is returned to the ControlSource of the combo may in fact be an autonumber rather than a string.

So, let's clean that up first. How many columns are in your RowSource query? If just one and it is text, then you have a string value but it still isn't a partial string. It is a complete string so change your LIKE's to =.

If the RowSource returns an ID and a string, then you may need to change your filter so that it uses the ID field so it is matching on SomeID = SomeID.

 

[MajP]

So may be wondering how am I able to build this complex filter from all of the controls with hardly any code? (And repeat this on any form with any amount of controls)?
Filter:

[Clinic Name]  = 'AlJabr'
AND [Brand]  = 'Fresenius'
AND [Machine Name]  = 'Hemodialysis Machine'
AND [Repair Status]  = 'Done'
AND [Date of Occurrence] BETWEEN #05/01/2024# AND #05/04/2024#

For each control I assign a variable, and one variable for the between filter

  Dim Clinic As String
  Dim Brand As String
  Dim Machine As String
  Dim Status As String
  Dim fltr As String
  Dim btwn As String

Then the function for any of the combos is simply

variable = GetFilterFromControl(Me.ComboName)

for clinic it is

Clinic = GetFilterFromControl(Me.Combo_Clinic)

The code uses DAO on the combo's recordset to determine the name of the bound column and the datatype of the bound column. Depending on the datatype it will properly delimit the value 'string', #date#, number, boolean. Therefore with only the combo it creates
[clinic Name] = 'aljabr'
(There are other parameters if you choose not to use the bound column or the filter field name in the combo is different from that in the form you are filtering)

The between function requires two textbox references and the fieldname and creates a properly delimited Between filter
The combine function allows you to then pass in each of the individual strings and combine them with And, OR and drops the empty strings.

There are more powerful features to get the filter from a multiselect listbox but equally as easy. These functions in the linked thread make doing multi control filtering super easy and require writing little code as demonstrated.

 

[KitaYama]

@MajP I'm terribly sorry for jumping in. Don't take this post as a criticize, But just some random ideas.

We do something like your method, but pass a string of fields separated by "," to the function. The function itself uses a loop to create the filter out of each field and concatenates them and returns the final result. It keeps the code much simpler specially when there are a lot of search textboxes, rather than having a lot of variables and calling the same function over and over for each variable.

    FieldList = "Field1,Field2,Field3"
    MyVariable1 = CreatFilter (FieldList,, flt_Equal......)
    FieldList = "Field4,Field5,Field6"
    MyVariable2 = CreatFilter (FieldList,, flt_LikeAnywhere......)

Your sample code shared in #3, doesn't show results for searching the records on an exact date. (Or I don't know how I should search)
Typing 2024/05/30 in From shows no records
Typing 2024/05/30 in both From and To shows no records

For searching dates and numbers, we normally pass ONLY From textbox. The function checks:
If From has a valid value and To is empty (Or both have the same value) then it returns a filter equal to that value.
If From is null and To has a valid value, then it returns a filter less than or equal to specified value.
If From and To both have valid different values, returns the between filter.
If From and To both are empty, returns nothing.

Of course it needs a tight naming rule,. For us, if there's a textbox searching for FROM (date or number), there's always a textbox with the same name ending with To. That's the main reason we pass only one textbox.

I can understand that you want to share your code with different developers and can not rely on naming. That's why you pass both From and To textboxes. But still I think you need a way to search for a specific date.

Thanks.

 

[MajP]

Typing 2024/05/30 in both From and To shows no records

I am not sure what you expected to see since there is no such date in the recordset
Tracker Query Tracker Query
But if you do a real date it works.

Date of Occurrence
19-May-24​
20-May-24​
20-May-24​
05-Dec-24​

If From has a valid value and To is empty (Or both have the same value) then it returns a filter equal to that value.
If From is null and To has a valid value, then it returns a filter less than or equal to specified value.
If From and To both have valid different values, returns the between filter.
If From and To both are empty, returns nothing.

If you want that behavior the modified code is below

Private Function FilterForm()
  Dim Clinic As String
  Dim Brand As String
  Dim Machine As String
  Dim Status As String
  Dim fltr As String
  Dim btwn As String
 
  Clinic = GetFilterFromControl(Me.Combo_Clinic)
  Brand = GetFilterFromControl(Me.Combo_Brand)
  Machine = GetFilterFromControl(Me.Combo_Machine)
  Status = GetFilterFromControl(Me.Combo_Status)
 
'---------- Modified Code
  If IsDate(Me.Date_From) And IsDate(Me.Date_To) Then
    btwn = GetBetweenFilter(Me.Date_From, Me.Date_To, "[Date of Occurrence]")
  ElseIf IsDate(Me.Date_From) Then
    btwn = GetFilterFromTextBox(Me.Date_From, sdt_date, "[date of occurrence]", flt_GreaterThan)
  ElseIf IsDate(Me.Date_To) Then
    btwn = GetFilterFromTextBox(Me.Date_To, sdt_date, "[date of occurrence]", flt_lessThan)
  End If
'------ Modified Code 

  fltr = CombineFilters(ct_And, Clinic, Brand, Machine, Status, btwn)
 
  If fltr <> "" Then
    Me.Filter = fltr
    Me.FilterOn = True
  Else
    Me.Filter = ""
    Me.FilterOn = False
  End If
 
End Function

but pass a string of fields separated by "," to the function. The function itself uses a loop to create the filter out of each field and concatenates them and returns the final result. It keeps the code much simpler specially when there are a lot of search textboxes, rather than having a lot of variables and calling the same function over and over for each variable

That may be OK for some simple textboxes all containing text. But I almost never have multiple textboxes in a search form. Normally some other type of controls like the user has here with combinations of combos, listboxes, and often multiple multi select listboxes. Can you handle, textboxes, listboxes, multiselet listboxes, to from combinations. options groups. Dates, Strings, Booleans, Nulls, Text with apostrophes, text with numerics, and foreign characters? If not then check the link.

 

[KitaYama]

I am not sure what you expected to see since there is no such date in the recordset

Just to clarify what I did :
I changed your code to read like this:

  If IsDate(Me.Date_From) And IsDate(Me.Date_To) Then
    btwn = GetBetweenFilter(Me.Date_From, Me.Date_To, "[Data_Entry]")
  End If

and this is what I had;

Clicking filter button showed no record.
I may have made a mistake. I'll test again.
Thanks.

Edit :
I hadn't time to see your database in #11.
I'll have a look as soon as possible.

 

[MajP]

Do you expect that
2024/05/30 11:24:31 pm would somehow fall between
2024/05/30 12:00:00 AM and 2024/05/30 12:00:00 AM?

 

[KitaYama]

Do you expect that
2024/05/30 11:24:31 pm would somehow fall between
2024/05/30 12:00:00 AM and 2024/05/30 12:00:00 AM?

No, I was telling you I tried to search for all data on 2024/05/30 and couldn't make the filter work. Normally when we search for a date, we don't enter the time. So if I search for 2024/05/30, I expect to see all records with that date (regardless of the time). I think it's obvious.
But forget about it. I'm OK with it and I Know how to do it. I simply thought you may want to check your code.
You know better than most of us. So, if you think there's no problem, then everything's just fine.

 

[Josef P.]

In my opinion, the instruction as to whether a time must be taken into account can only come from the application developer.
This is how I organise it in my filter class collection:

a) Developer decides that only the user input counts. The user must also set the time if required:
Criteria = SqlTools.BuildCriteria("DateField",SQL_Date,SQL_Between, date(), date())
=> DateField Between #2024-05-31# And #2024-05-31#

b) Developer decides that the user only has to enter the date, but that ‘until’ means the end of the day:
Criteria = SqlTools.BuildCriteria("DateField",SQL_Date,SQL_Between + SQL_Add_WildCardSuffix, date(), date())
=> DateField >= #2024-05-31# And DateField < #2024-06-01#
Note: ‘SQL_Add_WildCardSuffix’ is not a particularly good name, but I use it on purpose because it comes close to the text filter like ‘abc[B]*[/B]’ - the date filter process then symbolically reads: ‘2024-05-31*’.

 

[MajP]

So if I search for 2024/05/30, I expect to see all records with that date (regardless of the time). I think it's obvious.
But forget about it. I'm OK with it and I Know how to do it. I simply thought you may want to check your code.

I would not put that in the default code. The getbetween functions works with any datatypes not just dates. And you do not know if the user plans to search by time. So it would be a case by case. If I was doing this I simply add a line

If IsDate(Me.Date_To) Then Me.Date_To = DateAdd("s", 86399, Me.Date_To). Force the search to 11:59:59 of the to date.

Private Function FilterForm()
  Dim Clinic As String
  Dim Brand As String
  Dim Machine As String
  Dim Status As String
  Dim fltr As String
  Dim btwn As String
 
  Clinic = GetFilterFromControl(Me.Combo_Clinic)
  Brand = GetFilterFromControl(Me.Combo_Brand)
  Machine = GetFilterFromControl(Me.Combo_Machine)
  Status = GetFilterFromControl(Me.Combo_Status)
 
  If IsDate(Me.Date_To) Then Me.Date_To = DateAdd("s", 86399, Me.Date_To)
 
  If IsDate(Me.Date_From) And IsDate(Me.Date_To) Then
     btwn = GetBetweenFilter(Me.Date_From, Me.Date_To, "[Data_entry]")
  ElseIf IsDate(Me.Date_From) Then
    btwn = GetFilterFromTextBox(Me.Date_From, sdt_date, "[data_entry]", flt_GreaterThan)
  ElseIf IsDate(Me.Date_To) Then
    btwn = GetFilterFromTextBox(Me.Date_To, sdt_date, "[data_entry]", flt_lessThan)
  End If
 
  fltr = CombineFilters(ct_And, Clinic, Brand, Machine, Status, btwn)
 
  If fltr <> "" Then
    Me.Filter = fltr
    Me.FilterOn = True
  Else
    Me.Filter = ""
    Me.FilterOn = False
  End If
 
End Function