Filter data using combobox (2003)

Topic

New Reply

I have a form ( ViewComplaintsBySelection ) which has a combobox. When I select an entry from the combo box it runs a query to select records. This works correctly until I leave the combobox blank. At this stage NO records are returned – I want to display all records if it is blank.

The criteria for the forms is selecting data using the following:
IIf(IsNull([Forms]![ViewComplaintsBySelection]![SelectProduct]),Null,[Forms]![ViewComplaintsBySelection]![SelectProduct])

I’ve tried replacing the NULL in the middle of this query with:
“”
Like “*”
“XXXXX”
but nothing seems to allow the selection of all records via this.

I’ve also tried using =”” instead of IsNull for the first part of the criteria with no joy.

Any suggestions?

Reply | Quote

Replies

Set the Criteria as follows:

[Forms]![ViewComplaintsBySelection]![SelectProduct] Or [Forms]![ViewComplaintsBySelection]![SelectProduct] Is Null

When you save, close and reopen the query, Access will rearrange the criteria in two rows. This is normal behavior, it is not an error.

Reply | Quote

Thanks Hans – that part is now working, though when i went back to look at the criteria it only had one line saying [Forms]![ViewComplaintsBySelection]![SelectProduct]

I’m now adding 3 other combo boxes, SelectCategory, SelectType and SelectComplaint

I want to be able to use these along with the SelectProduct combo to filter the records. Any of these may or may not be null – when null it shouldn’t filter anything, when not null it should be used as the filter. I tried just adding the same criteria, with the names changed, but obviously it requires more than this.

Any thoughts?

Reply | Quote

I suspect that Access has added an extra column [Forms]![ViewComplaintsBySelection]![SelectProduct] to the query with Is Null in the second criteria line. Is that correct?

Reply | Quote

Sorry Hans – I’d added a few columns already so didn’t notice the extra column it had added.

I continued the form with 3 extra columns for which I added the same criteria (names changed). When I tried to run this, it would only work correctly if I had selected a product from the first list I’d set the combo box for. I checked the query and found that it had added a matrix of additional selections when I’d added the criteria for the 3 extra columns, but the product box wasn’t included in the bigger matrix. I removed all of the criteria and reapplied it to all 4 at once. This provided an even bigger matrix which then worked correctly. I’t all makes perfect sense once someone has shown you how!

Many thanks for you advice

Reply | Quote

Glad you found it. When adding multiple criteria, it’s often easiest to remove the existing criteria and start from scratch, as you have done. Building the “matrix” yourself is also possible, but it’s more difficult (and more work).

Reply | Quote