Solved

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

Posted on 2014-01-14
21
577 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 35

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 35

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 35

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 35

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 35

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 35

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

810 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