Solved

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

Posted on 2014-01-14
21
585 Views
Last Modified: 2014-01-17
I have a combo box on a form. I need the user to be able to select Open, Closed, or blank for both. Open and closed works perfect. How do I allow them to select blank or perhaps all for everything. A query is opened based on what is selected on the combobox.
0
Comment
Question by:marlind605
  • 8
  • 7
  • 6
21 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39779959
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.
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 300 total points
ID: 39779994
I use the empty option to mean all when I use combos for searches.  However, this SQL isn't quite right:
SELECT * from yourTable
WHERE [SomeField] = [Forms]![FormName]![comboName]
OR [Forms]![FormName]![comboName] & "" = ""

"" is a zero length string and if nothing has been entered into the combo, it would be null.  So change the query to:

SELECT * from yourTable
WHERE [SomeField] = [Forms]![FormName]![comboName]
OR [Forms]![FormName]![comboName] is null
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39780034
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:marlind605
ID: 39780048
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?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39780084
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"
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 200 total points
ID: 39780086
You are still going to have to requery the form or whatever object uses that SQL in the AfterUpdate event of the combo, or in a command button.

If you use a command button, I prefer to simply build the criteria clause in code and set the forms Filter Property.  That way, I don't even need to reference the field in the criteria if the user has selected "All".
0
 

Author Comment

by:marlind605
ID: 39780144
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?
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39780191
I use the syntax I mentioned above, which handles both NULLs and ZLS.
I read it too fast and didn't notice the &.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39780197
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

Open 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 & "'"

Open in new window

would be interpretted as:
varCriteria = (NULL + " AND ") & "[Status] = '" & me.cbo_Status & "'"

Open in new window

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

Open in new window

and finally as:
varCriteria = "[Status] = '" & me.cbo_Status & "'"

Open in new window

But if cboFY had a value, then:
varCriteria = (varCriteria + " AND ") & "[Status] = '" & me.cbo_Status & "'"

Open in new window

might actually be interpretted as:
varCriteria = "[Fiscal Year] = '2013' AND [Status] = '" & me.cbo_Status & "'"

Open in new window

0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39780199
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.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39780288
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.
0
 

Author Comment

by:marlind605
ID: 39782096
Thanks Pat and Dale. My original question was answered but I value the followup answers and will be testing them today. Mark
0
 

Author Comment

by:marlind605
ID: 39788708
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.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39788841
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39788854
If [CatID] is numeric, that line should read:

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

Author Comment

by:marlind605
ID: 39789067
In response to pat
"AND [cbocategory] Like '3"
0
 

Author Comment

by:marlind605
ID: 39789083
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?
0
 

Author Comment

by:marlind605
ID: 39789093
If I select FYDateRX, Status and Stage it works but If I just want to select status or stage it gives error message.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39789230
The filter can't start with an AND plus there is no ending single quote after the 3.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39789507
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
0
 

Author Comment

by:marlind605
ID: 39790127
I think I got what I need. Thank you Pat and thank you Dale.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question