Majp Search and Filter

[ClaraBarton]

I'm using Majp's MultiControl Search to create a search form.
My object is to filter a subform with numerous fields.
1. I have a search box to type in whatever I'm searching for.
2. A listbox with a list of fields to search in... $25 in Amount, Jones in Name, etc.
3. A listbox with the various filters... = >=, etc.

I added a field to the field listbox that tells what data type each field is but I don't understand how to use it with the CSql function.
Or any other way.
I use the function GetFilterFromControl and CombineFilters but I can't figure how to
get a data type from the search box.

 

[MajP]

can you provide the demo form and some records?

 

[ClaraBarton]

How's this?

 

[MajP]

I already demoed it. See frmDemoClara.
I had to create a new function: called GetSql_DataTypeFromForm in the mdlControlFilters. Once you have that then this is all that is needed in the form

Private Function FilterForm()
    On Error GoTo FilterForm_Error
  Dim DT As SQL_DataType
  Dim strFilter As String
  Dim DelimitedText
  If Not IsNull(Me.cmboFT) And Not IsNull(Me.cmboField) And Not IsNull(Me.txtSearch) Then
   'determine the fields data type
   DT = mdlControlFilters.GetSQL_DataTypeFromTable("tblDemoUnbound", Me.cmboField)
   'Get filter based on data type
   DelimitedText = CSql(Me.txtSearch, DT)
   strFilter = Me.cmboField & " " & mdlControlFilters.GetSQL_Filter(Me.cmboFT, DelimitedText)
   Me.Filter = strFilter
   Me.FilterOn = True
  Else
   Me.Filter = ""
   Me.FilterOn = False
  End If
   
    On Error GoTo 0
    Exit Function

FilterForm_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FilterForm, line " & Erl & "."

End Function

If you know the field to search you can determine its data type by DAO. If you know the datatype you can use my function to delimit it properly for Dates, strings, numerics, and booleans. Once you can delimit the value you can filter any field.

 

[ClaraBarton]

frmDemoClara?

 

[MajP]

Try now.

 

[ClaraBarton]

Oh My! This works beautifully. Instead of the field for data type, I changed the field to list the table it originated from and it's perfect! I apologize that my form had no data. I forgot to localize the tables. Thank you SO much!

 

[MajP]

I updated the demo to be a little more understandable. I added a menu and did formatting. There was so much going on I was confused on what I was trying to demonstrate.