No, you phrased your question properly and I understood what you wanted to do.
If it is not working and you understood what I said, it should work.
As I said, I suspect that the value of your combobox is not really "ALL" when
that is showing as the choice. Can you post the row source of the combobox?
(Copy and paste it, don't retype it).
For TESTING purposes you might try
SELECT *
FROM [qry flm_gained]
WHERE Nz([Forms]![Form1]![Combo38],"ALL")
NOT IN ("Option1","Option2","Option3");
THat should return no records if you select option1, 2, or 3 and all records
otherwise.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I think I may have phrased the question incorrectly...All is an option in the
combo box because I added it, but the field firstline_service will not
contain any records with the word "all" in it. What I would like to do is if
"all" is chosen from the combo box I would like the query to run as if there
is no criteria...a complete recordset excluding no records. I hope this
clarifies what I am looking for.
-John
:
The only thing I can think of is that the combobox value is not "All",
but is some other value - Perhaps All with a leading or trailing space
or you have a multi-field combobox and the bound column is not the one
you are seeing.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
H0MELY wrote:
Thank you so much for looking, Unfortunately I couldn't get that to work.
here is the exact sql string I used...
SELECT *
FROM [qry flm_gained]
WHERE ([qry flm_gained].firstline_service Like "*" &
[Forms]![Form1]![Combo38] & "*" OR [Forms]![Form1]![Combo38] = "All");
The table is obviously called [qry flm_gained], the combo box is Combo38 and
the field that I am trying to match is called firstline_service. Searching
for option1, option2 and option 3 work just fine, but All returns an empty
set of data.
what amd I doing wrong? Thank you again for your help. -John
:
SELECT *
FROM SomeTable
WHERE (SomeField = Forms!YourFormName!YourCombobox
OR Forms!YourFormName!YourCombobox = "All")
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
H0MELY wrote:
Thank you all for looking. Basically I have a form with a pre-populated
combo box and a command button. Lets say the combo box values are All,
Option1, Option2 and Option3. When the command button is clicked it will run
a select query with whatever option is chosen as the criteria. What I would
like is to have "All" actually return all results. Hopefully this is
something easy.
In the future I would actually like the combo box values to be based off a
grouped select query..again having "All" added to the combo box values and
when selected the query would return all results. This is a wish list item,
the top paragraph is what is really important to me now.
Thank you to everyone that looked and know that your words of wisdom will be
GREATLY appreciated.
-John
