Search bar filtered by different columns (1 Viewer)

[vent]

Hi everyone

So I have a working search bar that filters data from a listbox based on what the user types in. The list box has 5 columns, 2 are text, 2 are date/time and 1 is yes/no data types. The search bar filters based the first column (company name) and the criteria is as follows in the query:

Like "*" & [forms]![frmMain]![SrchTxt] & "*"

I'm just wondering how do I filter columns based on different data types (each column)? I know I need to add more but for the second free text column, I copied the same criteria in the first but nothing came up in the search bar. if anyone knows how to do this any sort of guidance is MUCH appreciated!

 

[CJ_London]

"*" is a text wildcard. It will work with numbers as well so

like '*123*' will find

123
2123
33123
31234

etc

But dates are also a number (today is 42882) just formatted to look like dd/mm/yyyy and you cannot search for a value based on its formatted presentation without turning that value into a formatted string

today is 27/05/2017 so

datefield like '*/05*"

will not find the date. Instead you need

format(datefield,"dd/mm/yyyy") like '*/05*'

 

[vent]

Thank you for the reply. I will play around with the date field format. Much appreciated!

However do you know why when I copy Like "*" & [forms]![frmMain]![SrchTxt] & "*" into the second column field which is also text (e.g. business owner's name) nothing appears in the search bar? I know "*" is a text wildcard but why doesn't it filter data from the second column as well?

 

[vent]

Also, do I need to create more queries? Or can I use the same query just have a different criteria under each field?

 

[almahmood]

Also, do I need to create more queries? Or can I use the same query just have a different criteria under each field?

Concat all your fields like below and then use that field as criteria.

SELECT EmployeeID, EmployeeName, DOB, Address, City, State, Zip, Cell
FROM tblEmployees
WHERE EmployeeID & "|" & EmployeeName & "|" & DOB & "|" & Address & "|" & City & "|" & State & "|" & Zip & "|" & Cell "'*" & [forms]![frmMain]![SrchTxt] & "*'"

Now you can search by any keyword.

 

[vent]

Thank you! That worked. Now my next question is I was thinking of adding a dropdown for the user to select a list of date ranges (Oldest, 2005-2010, 2010-2015, Recent, etc) and the list box filtering based on what the user selects. I'm not sure how about to go about this, I know i need to do some VBA but does anyone have an example? Even if it's the most basic starting point, that would be very beneficial. I tried going online but I haven't found anything related yet. Any feedback is much appreciated!

 

[almahmood]

Thank you! That worked. Now my next question is I was thinking of adding a dropdown for the user to select a list of date ranges (Oldest, 2005-2010, 2010-2015, Recent, etc) and the list box filtering based on what the user selects. I'm not sure how about to go about this, I know i need to do some VBA but does anyone have an example? Even if it's the most basic starting point, that would be very beneficial. I tried going online but I haven't found anything related yet. Any feedback is much appreciated!

I would prefer two text boxes which will hold from and to date and use them in criteria. Dropdown will work too but that needs some more VBA coding and of course it won't be dynamic like text boxes.