Solved

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

Posted on 2014-01-14
21
574 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 34

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
 

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 34

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 34

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 34

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 34

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 34

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now