Creating an All option in a combo box that will pull all records

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

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

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

"John Spencer" wrote:


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

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

"John Spencer" wrote:

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

On Thu, 8 May 2008 14:50:00 -0700, 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

What is the Rowsource query of the combo box, and what are the actual values
in that row source?
--

John W. Vinson [MVP]

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

"John Spencer" wrote:

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

"John Spencer" wrote:

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

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

H0MELY wrote:
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

"John Spencer" wrote:

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

"John Spencer" wrote:

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

Again thank you for trying to help =) Here is the rowsource...

"All";"Option1";"Option2";"Option3"

I tried your testing item and it returned no items for all 4 choices. I am
going to create a database with 1 form and 1 table and 1 query to see if I
can get it running. Would you be interested in seeing it? I am not sure why
this is causing a problem to me. Thanks again for all of your help.

-John

"John Spencer" wrote:

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

H0MELY wrote:
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

"John Spencer" wrote:

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

"John Spencer" wrote:

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

Okie, something weird is going on...I created that test database and
everything worked just fine based on the initial suggestion you had...so
there must be something I have done in the original that I am not accounting
for. I'm going to play a bit and find out where I have my oversight. Your
suggestion was accurate, I am just a bonehead. I really do appreciate the
help =)

-John

"H0MELY" wrote:

Again thank you for trying to help =) Here is the rowsource...

"All";"Option1";"Option2";"Option3"

I tried your testing item and it returned no items for all 4 choices. I am
going to create a database with 1 form and 1 table and 1 query to see if I
can get it running. Would you be interested in seeing it? I am not sure why
this is causing a problem to me. Thanks again for all of your help.

-John

"John Spencer" wrote:

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

H0MELY wrote:
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

"John Spencer" wrote:

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

"John Spencer" wrote:

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