List Box With Multiple Filters

[iheartvba]

Hi,
I have a form where users have to go through a lot of data. The data is being viewed in a list box. This is because they need to be able to select one of the items in the list. The list box will pick up filter criteria from text boxes on the form. Basically a user may want to use different criteria to do there search. At the moment I have got Date and Amount so for date I want to give the option where the user can leave the date blank or enter a date, and if the date is blank then no date filter should be applied.

*The following code is in access query builder, I had put this into the criteria section of the Builder.

iif(isnull(Forms!FrmFilter!tbDate),>#01/01/1900#,Forms!FrmFilter!tbDate)

This does not seem to work, it filters properly when I put a date in, but not when the text box is null. What it should do is allow me to fill in either both amount and date criterias or either date or amount or fill in no criteria at all and filter the listbox accordingly.


Thanks in advance

[woodey2002]

Hi mate

I found a great example on the site below maybe it can help you also.

Regards, JAMES
*** Edit - Illegal link removed ***

[NeoPa]

Sorry James. I know you're new, but we don't allow links to competing forums here (Check out the rules via the Help link on every page). Microsoft links are fine for Microsoft applications, but for others we need content rather than a link.

-Administrator.

PS. Appreciate you're just trying to help of course.

[NeoPa]

You should find all you need to know in Example Filtering on a Form.

This refers to filtering for data on a form, and your question is about filtering a ListBox control instead, but the fundamental filtering should be the same. You still add the item to the filter only if a value to filter is selected.

The main difference is that whereas a form can be filtered using a string parameter (WhereCondition) in the call to open the form, in a ListBox control the filtering would form the WHERE clause of the RecordSource SQL.

[woodey2002]

No problem.

Thanks for the advice.

Won't do again.

Kind regards,

James.

[ChipR]

Try changing

>#01/01/1900#

To

">#01/01/1900#"

Or

""

[iheartvba]

Thanks everyone here's a summary of what I've learnt and succesfully implemented. If you want to pass criteria to a listbox, here is an easy way to do it.
Step 1: Write your SQL string
Step 2: Pass it to the list box i.e. me.listbox.rows ource = (the SQL string you have written

I really do heart VBA :D