Access Query Parameter Filtering Form

Question

I am filtering a form named sfrWorklistFilter from a combo box named cboOpeningType. The recordsource is from an embedded query on the form. If I make a selection from the combo box the filter works fine with the following code:

Forms![sfrWorklistFilter]![cboOpeningType]

However I need to return all records when no selection is made in which case I use the following code:

Like Forms![sfrWorklistFilter]![cboOpeningType]  & "*"

The filter then does not give exact matches, but all records that begin with the letter on the combo box.

I need exact matches for the record or if no selection is made all records.

Any suggestions?

Confused? When no selection is made, why would the combo box have a letter selected? — dbmitch
– dbmitch, Commented Jul 27, 2016 at 20:19
I think i see now what you mean - yiu're trying ot set a fixed Filter property - not in VBA code of the cboOpeningType AfterUpdate event, correct? — dbmitch
– dbmitch, Commented Jul 27, 2016 at 20:24
@dbmitch you are correct I am not setting a filter property in VBA. I was trying to set the SQL inside the embedded query of the form using the combo box value. I have three combo boxes and trying to filter three fields according to their value. If I don't enter a value I want to return all records of that field. The rowsource of the combo box gives a single letter selection as one of the values. When that letter is selected all records that begin with that letter. When nothing is in the combo box I get no results at all. — John Wolfenstein
– John Wolfenstein, Commented Jul 27, 2016 at 21:08
Still doesn't make sense with what you're showing above. You need at least one field in your form filter - you have none. And that has to change with each combo box apparently. — dbmitch
– dbmitch, Commented Jul 27, 2016 at 21:12
@dbmitch I found it as well. It took me little while. The "filter" works great. Thanks for the time. — John Wolfenstein
– John Wolfenstein, Commented Jul 27, 2016 at 21:47

dbmitch · Accepted Answer · 2016-07-27 21:42:31Z

1

EDIT remove double quotes

This should work - and you can do same thing with your other field/combobox searches

Like IIf([Forms]![sfrWorklistFilter]![cboOpeningType]<>"",[Forms]![sfrWorklistFilter]![cboOpeningType],"*")

edited Jul 27, 2016 at 21:42

answered Jul 27, 2016 at 20:37

dbmitch

5,4064 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access Query Parameter Filtering Form

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related