Microsoft Access Combo Box How to show Status - Open Closed or both

I generally use a Union query to include something like "All Values" as the first item in the combo box, instead of the blank.

Then, my query would have SQL that looks like:

SELECT * from yourTable
WHERE [SomeField] = [Forms]![FormName]![comboName]
OR [Forms]![FormName]![comboName] = "All Values"

But you could use the empty value as the "all values" in which case, the query might look like:

SELECT * from yourTable
WHERE [SomeField] = [Forms]![FormName]![comboName]
OR [Forms]![FormName]![comboName] & "" = ""

The latter part of this 2nd query makes sure that if the combo is either NULL or an empty string, it will match in the comparison to the empty string.

Pat,

There are circumstances, where a combo box that contained a value which has subsequently been deleted might actually contain a zero length string (ZLS) instead of a NULL.  Generally, that is when the combo is bound, not unbound, but for consistency sake, and to cover all bases, I use the syntax I mentioned above, which handles both NULLs and ZLS.

I think the user wants to select Open Closed or All. If All is selected all the records should be displayed in the query. How can I do that?

Simplest way is to base the combo on a "ValueList" as the Row Source Type and enter values "All"; "Open"; "Closed" as the RowSource for the combo.  Then use the SQL I showed in my first post:

SELECT * from yourTable
WHERE [SomeField] = [Forms]![FormName]![comboName]
OR [Forms]![FormName]![comboName] = "All"

Dale Fye, There will be multiple selections on the form. Fiscal Year is another selection. They select fiscal year or leave it blank, then they can select open closed or all. I thought about using filter by form can you provide more details?

I use the syntax I mentioned above, which handles both NULLs and ZLS.

I read it too fast and didn't notice the &.

This is not truely a filter-by-form functionality, but is similar.

When I have more than one field I want to search by (maybe a textbox and one or more combos), I generally include a Filter button (cmd_Filter).  The code in the click event of that button evaluates all of the filter controls and assesses whether the field associated with that control needs to be included in the criteria.  So, lets assume three controls cbo_FY, cbo_Status, and txt_Comments.  The code behind the cmd button would look like:

Private Sub cmd_Filter_Click

    Dim varCriteria as variant

    'I use the same button to filter and clear the filter
    If cmd_Filter.Caption = "&Clear Filter" then
        me.Filter = ""
        me.FilterOn = False
        me.cmd_Filter.Caption = "&Filter"
        Exit Sub
    End IF

     'Process the filter
     if me.cbo_FY & "" <> "" Then
         varCriteria = "[Fiscal Year] = '" & me.cbo_FY & "'"     'assumes FY is a string
        'varCriteria = "[Fiscal Year] = " & me.cbo_FY              'assumes FY is numeric
     endif

     if me.cbo_Status & "" <> "" Then
         varCriteria = (varCriteria + " AND ") & "[Status] = '" & me.cbo_Status & "'"
     endif

     if me.txt_Comments & "" <> "" Then
         varCriteria = (varCriteria + " AND ") & "[Comments] Like '*" & me.txt_Comments & "*'"
     endif

     if isnull(varCriteria) = False Then
         me.Filter = varCriteria
         me.filterOn = true
     else
         me.FilterON = false
     end if

End Sub

Select allOpen in new window

This would build a filter string for the form and then apply it based upon the values in those combo boxes.  If the combo is left blank as mentioned by PatHartman, you would simply ignore those controls as you build the criteria string.  Otherwise, you would add the to the overall criteria.

To explain my syntax a little.  A variant variable is instantiated as a NULL value.  Any time you use the plus sign (+) to concatenate a string to a NULL, the result will be a NULL.  So if  the first combo is blank, when the code gets to the 2nd test above varCriteria will still be NULL.  Therefore the syntax:

varCriteria = (varCriteria + " AND ") & "[Status] = '" & me.cbo_Status & "'"

Select allOpen in new window

would be interpretted as:

varCriteria = (NULL + " AND ") & "[Status] = '" & me.cbo_Status & "'"

Select allOpen in new window

then as:

varCriteria = NULL & "[Status] = '" & me.cbo_Status & "'"

Select allOpen in new window

and finally as:

varCriteria = "[Status] = '" & me.cbo_Status & "'"

Select allOpen in new window

But if cboFY had a value, then:

varCriteria = (varCriteria + " AND ") & "[Status] = '" & me.cbo_Status & "'"

Select allOpen in new window

might actually be interpretted as:

varCriteria = "[Fiscal Year] = '2013' AND [Status] = '" & me.cbo_Status & "'"

Select allOpen in new window

When you want to use multiple conditions, any one of which may be empty meaning "all"

Select ...
From ...
Where (fld1 = Forms!yourform!fld1 OR Forms!yourform!fld1 & "" = "")
AND (fld2 = Forms!yourform!fld2 OR Forms!yourform!fld2 & "' = "")
AND (fld3 = Forms!yourform!fld3 OR Forms!yourform!fld3 & "" = "");

I don't use filters since they work best when the ENTIRE recordset is loaded.  The better client/server technique is to use criteria in your query and Requery the form to bring up the next set of records.

In the above example, the parentheses are critical since the expressions are compound and include both AND and OR.  We are OR ing the conditions within a set of parthenthes and AND ing the expression groups.

I also occasionally use code such as what Dale posted but I don't use it to create a Filter, I use it to create a where clause which I use to replace the form's RecordSource.  Given that most of my databases are SQL Server, I simply use client/server techniques all the time.  That way if an ACE app needs to be upsized, I don't have any real work to do to make it happen.  I simply upsize the BE and I'm good to go.  I rarely have to make any other changes.

Thanks Pat and Dale. My original question was answered but I value the followup answers and will be testing them today. Mark

Dale,
I've been working with my form. Using your code what do I do if I just want to select 1 combo box area. I get a run time error 3075 syntax error (missing operatory in query expression and [catid] like 2.
 Dim varCriteria As Variant

    'I use the same button to filter and clear the filter
    If cmd_filter.Caption = "&Clear Filter" Then
        Me.Filter = ""
        Me.FilterOn = False
        Me.cmd_filter.Caption = "&Filter"
        Exit Sub
    End If

     'Process the filter
     If Me.cboFYRX & "" <> "" Then
         varCriteria = "[FYDateRX] = '" & Me.cboFYRX & "'"     'assumes FY is a string
        'varCriteria = "[Fiscal Year] = " & me.cbo_FY              'assumes FY is numeric
     End If

     If Me.cboStatus & "" <> "" Then
         varCriteria = (varCriteria + " AND ") & "[Status] = '" & Me.cboStatus & "'"
     End If
 
     If Me.cbostage & "" <> "" Then
         varCriteria = (varCriteria + " AND ") & "[Stage] Like '" & Me.cbostage & "'"
     End If
    If Me.cboState & "" <> "" Then
         varCriteria = (varCriteria + " AND ") & "[State] Like '" & Me.cboState & "'"
     End If
      If Me.cbocategory & "" <> "" Then
         varCriteria = (varCriteria + " AND ") & "[catid] Like '" & Me.txtcatid & "'"
     End If
   If Me.txtrequester & "" <> "" Then
         varCriteria = (varCriteria + " AND ") & "[DBrequestersdetails]" = Me.txtrequester
     End If
    If Me.txtorganization & "" <> "" Then
         varCriteria = (varCriteria + " And ") & "[DBORGID] = " & Me.txtorganization              'assumes FY is numeric
     End If
     If IsNull(varCriteria) = False Then
         Me.Filter = varCriteria
         Me.FilterOn = True
     Else
         Me.FilterOn = False
     End If
or should I post another question.

Put a stop in the code on "If IsNull(varCriteria)" and print the contents of varCriteria so we can see if it is syntactically correct.

If [CatID] is numeric, that line should read:

If Me.cbocategory & "" <> "" Then
    varCriteria = (varCriteria + " AND ") & "[catid] =" & Me.txtcatid
End If

In response to pat
"AND [cbocategory] Like '3"

And based on the code I put down, should I be able to select just one of the items and get what I selected from the filter?

If I select FYDateRX, Status and Stage it works but If I just want to select status or stage it gives error message.

The filter can't start with an AND plus there is no ending single quote after the 3.

if varCriteria is declared as a variant, it should be instantiated as a NULL, but just in case, add the following line just before your

'process the filter

line:

varCriteria = NULL

if varCriteria starts out as NULL and the code is written with:

varCriteria = (varCriteria + " AND ") & "[catid] Like '" & Me.txtcatid & "'"

Then the value of varCriteria after processing the value in cboCategory cannot be:

"AND [CatID] Like '3'"

NULL + " AND " = NULL

you might want to stick debug.print lines in every one of those if statements.  My guess is that there is a space in one of those combo boxes or textboxes.  You might also want to try changing all of the IF statements to something like:

IF TRIM(Me.controlName & "") <> "" Then

This would extract all of the spaces from the combo and textboxes

I think I got what I need. Thank you Pat and thank you Dale.