Filtering a Datatable

[CrazyCanuck]

I want to setup a form that basically shows the main datatable with some filter options.

So basically the datatable will be in a subform and have text boxes above each colum that the user can enter a value into (instead of the default *) to filter the list.


* * * *
-----------------------------------------------------------------------------------
Date Line Area Description
10/30/03 33 north dasbdibffsad
5/30/04 21 south asbdjsdcfsda
etc etc

if a user changed one of the *s, it would filter the datatable accordingly.

Anyone know of sample code that I could use to get an idea of how to do this?

 

[Uncle Gizmo]

I find this an effective way. See attached

 

[chuckcoleman]

Uncle Gizmo,

Thank you for providing the example of how to use multiple combo boxes to filter records in an Access database. It’s a very useful tool if you want to search with multiple criteria from within a Form. HOWEVER, ONE PART OF IT DOESN’T WORK. If a record has a Null value in a field that is used by one of the combo boxes that does the filtering, it excludes those records. When all combo boxes are cleared, I would expect the Form to show ALL records in the query/table. It doesn’t. It shows all records that DO NOT have a null value in the field(s) that are used by the combo boxes.

Can you please relook at your code and offer a solution so all records are returned if all combo boxes are blank/null? I experimented with a couple of different ideas in the module but it didn’t help. Your sample database exhibits the same issue.

Thank you in advance for your help.

 

[Uncle Gizmo]

You shouldn’t really Use Null values in your data, because it doesn’t mean anything, that is what “Null”describes, an unknown.

For example:
If you don’t have any products in stock, then you do not have Null products, you have 0 (zero) products.

If you don’t have a customer’s address details, then you don’t want to store a Null value, you actually have some information, this customer’s address has not yet been collected, or possibly this customer does not have a fixed abode, not the sort of customer you want to be dealing with!

So I would suggest that you replace the Null’s in your tables with real values with a real meaning and this should sort out your problem.

Please either reply here or there, but not both places as this causes me unnecessary inconvenience http://msaccesshintsandtips.ning.com/profiles/blogs/allow-a-combo-box-to-return