Using Like"*"&[form]&"*" in a query

[Turps]

Hello,

I have a form that uses combo boxes to filter a subform.
The field in the table associated with the subform is joined to the primary key (autonumber) of another table.

The combo box in form is meant for show all, or filter none when the combo box is a null value.

I use LIKE"*"&[form]&"*" which worked really well until I added a 10 and 11 primary key reference table.
Now, when I filter for 1, 10 and 11 are also displayed because 10 and 11 are like 1.

Is there a better way to filter none (show all) in the subfolder?

 

[Deleted member 147267]

I suppose the field in the reference table and the other table are text data type? That would explain it. Is there a better way? I'd suggest that the combo has text options that are displayed in the visible column so that you can have "ALL" and whatever else. The first (invisible column) would be for linking to whatever table field that matches it. I'd probably apply a filter to the form based on the first column rather than tweak the sql of its recordsource.

 

[theDBguy]

Hi. Yes, there is a way to do that, but it's probably better if you could show us what you got, so we can tell you if there's a better approach than what you're already doing/trying to do.

 

[Turps]

Here is a sample of the problem.
if you select John (1) from the combo box, the query returns John and Frank, because Frank is (10) and 10 is like 1 I suppose.

At first I tried applying the criteria or Is Null; however that only appears to work when there is one field to which you are applying criteria.
The actual DB has three separate combo boxes for three separate fields.

 

[Turps]

Sorry, slight error in what I was trying to show. Here is the correct version and a version with two comboboxes.

 

[vba_php]

turps,

your sample works fine, as you want, to return all records when the combo is NULL. see attached image. as far as adding a ''ALL'' option to the box, perhaps this will help:

https://docs.microsoft.com/en-us/of...pts/controls/addallto-a-combo-box-or-list-box

even though that article is writing code in .NET language, it came from VB6 which is the same as VBA in almost every aspect of constructs.

 

[plog]

First, you don't really need a subform, just stick the combo boxes in the header of a regular form with the data down below. Makes it a little less complicated.

Then, the best way to filter the form is not via the query, but with VBA. Specifically the Form.Filter property to filter it:

https://docs.microsoft.com/en-us/office/vba/api/access.form.filter(property)

Remove the criteria from your query. Then add VBA so that you build a filter string using the users input data then turn the filter on. If they don't input data, you just turn the filter off.