Combo box selection

[SunTop]

Hello everyone I have a query that is being filtered based on the value of a combo box in another form. So in the design view of the query I have something like this [Forms]![FormName]![ComboBoxName] in the Criteria of the field I am filtering my query with. The combo box in the form is an unbounded control that picks its value from another table. So once the user clicks a command button on the form, a report is being generated based on the filtered query. I need to give my users and option to select a value from the combo box that will return the underlying query of the report unfiltered and I don’t want to use blank and the option should be user friendly (not something like null)

[CJ_London]

in your criteria

WHERE somefield = [Forms]![FormName]![ComboBoxName] OR [Forms]![FormName]![ComboBoxName] is null

Or if you include 'All' in your combo rowsource

WHERE somefield = [Forms]![FormName]![ComboBoxName] OR [Forms]![FormName]![ComboBoxName] ="All"

[SunTop]

Thanks for your reply Ajax
The combo box picks its values from a table, Do I have to add “All” to my rowsource table ? can I add “All” to the values that are displayed in my combo box and not add that value to my table or change the rowsource of my combo box ?

[aytee111]

I've never used "All" as Ajax is suggesting, I am interested to hear how (s)he does it. I usually add an option button for ALL and then in the query say OR Forms!FormName!Option=True.

[SunTop]

Thanks for your reply aytee111
Could you please elaborate on your answer, how do you combine the use of the option button with the combo box ? and what do you do if you had more than one combo box that is used to feed the query in multiple criteria fields ?

[pbaldy]

Here's a method of adding all as a choice:

http://access.mvps.org/access/forms/frm0043.htm

you can combine that with the null method of returning all records.

[SunTop]

Thank you pbaldy for your reply
That is exactly what I was searching for

[pbaldy]

Happy to help!

[CJ_London]

@aytee111

I've never used "All" as Ajax is suggesting, I am interested to hear how (s)he does it.

- I'm a he

when using a table as a rowsource. For a straight list you would use something like

SELECT fld1, 1 as SortOrder
FROM myTable
UNION SELECT "All", 0 FROM myTable
ORDER BY sortorder, fld1

or where there are ID's involved, something like

SELECT PK, fld1, 1 as SortOrder
FROM myTable
UNION SELECT 0, "All", 0 FROM myTable
ORDER BY sortorder, fld1

in which case your criteria would be

WHERE somefield = [Forms]![FormName]![ComboBoxName] OR [Forms]![FormName]![ComboBoxName] = 0

The dummy sortorder field is required to ensure that All appears at the top (or the bottom if you change it's value to 2 for the second union query), regardless of the sorting for the rest of the values (I presume you wouldn't want it to appear between Alaska and Arizona). It does not need to be referenced in the combo or listbox so the column count would remain unchanged.

Edit: just seen Paul's link, you can substitute a null for 0 in the second example

...
...
UNION SELECT Null, "All", 0 FROM myTable
...

[aytee111]

Of course! That's brilliant, and very useful. Thanks Mr Ajax