Link to home
Start Free TrialLog in
Avatar of mijwil

asked on

Newbie Needs Help Filtering MSAccess Form

I apologize in advance for my knowledge base. I am decent at developing databases using all of the canned features and query tools in Access. I am trying to learn VBA.

I am trying to filter 2 fields using as input the selections in a drop down combo box and an option box.

I have a combo box [CustomerSelect] that filters a customer name. (which by the way I don't completely understand the ampersand usage. another topic)

Me.Filter = "CUS_CorpName = """ & Me.CustomerSelect & """"

That works fine. Now I am trying to filter that further with my option box.

I have an option box [WOStatus] to select "Released" or "Complete" work orders using the values 1 or 2. The field I am trying to filter is [WKO_StatusCode]

If Me.WOStatus = "2" Then
        Me.Filter = "WKO_StatusCode= 'Complete'"
        Me.FilterOn = True

Can you help me with the correct syntax to filter both fields in one statement?
Inexperience and confused.
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mijwil


That worked. Thanks for your help.
Can you explain when I should use of the ampersand? Can't seem to get a handle on that.

Baby steps.
<Can you explain when I should use of the ampersand? Can't seem to get a handle on that. >
Not an easy answer here...

The Ampersand is the "Concatenation" character in VBA.

So anytime you want to "Join" two expressions, you would use the ampersand.

The Kicker is that you need to remember that most of the time you need a space bar character between the expressions...
Expression1 & " " & Expression2

(When to use Single and Double quotes is a whole other subject...)

In your case since you were needing two Filters, you also needed the word "And" concatenated in as well.

FilterOne & " And " & FilterTwo


Avatar of mijwil


Thanks. As odd as it is, I actually understand. That's scary.