Adding * to a combo box

[ReportSmith]

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.

 

[Jack Leach]

http://www22.brinkster.com/accessory/forms/007.shtml

Take a look at that. It's the only thing I came up with on short notice but
seems like a place to start (and maybe finish)

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

http://www22.brinkster.com/accessory/forms/007.shtml

Take a look at that. It's the only thing I came up with on short notice but
seems like a place to start (and maybe finish)

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[fredg]

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.

How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.

 

[fredg]

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.

How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.

 

[ReportSmith]

Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.

How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.

 

[ReportSmith]

Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.

How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.

 

[John Spencer MVP]

One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.

How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.

 

[John Spencer MVP]

One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.

How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.

 

[ReportSmith]

Thanks. (I already had the DISTINCT statement).

One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

On Mon, 18 May 2009 07:04:02 -0700, ReportSmith wrote:

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.

 

[ReportSmith]

Thanks. (I already had the DISTINCT statement).

One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

On Mon, 18 May 2009 07:04:02 -0700, ReportSmith wrote:

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.

 

[John Spencer MVP]

If you use the UNION conjunction then there is no reason to use DISTINCT in
the query. UNION removes duplicates from the entire set of records - just as
distinct does.

I also goofed since if I use UNION ALL, I do need to either use Distinct on
both of the queries or use UNION and let it do the work.


Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select DISTINCT "<ALL>" from VerySmallTable
Order by [FieldName];

Or


Select YourTable.[FieldName] From YourTable
UNION
Select "<ALL>" from VerySmallTable
Order by [FieldName];

You could use the first version I posted if VerySmallTable had just one record
in it. Or if you limited it to one record by using a where clause.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks. (I already had the DISTINCT statement).

One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

:

On Mon, 18 May 2009 07:04:02 -0700, ReportSmith wrote:

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.

 

[John Spencer MVP]

If you use the UNION conjunction then there is no reason to use DISTINCT in
the query. UNION removes duplicates from the entire set of records - just as
distinct does.

I also goofed since if I use UNION ALL, I do need to either use Distinct on
both of the queries or use UNION and let it do the work.


Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select DISTINCT "<ALL>" from VerySmallTable
Order by [FieldName];

Or


Select YourTable.[FieldName] From YourTable
UNION
Select "<ALL>" from VerySmallTable
Order by [FieldName];

You could use the first version I posted if VerySmallTable had just one record
in it. Or if you limited it to one record by using a where clause.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks. (I already had the DISTINCT statement).

One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

:

On Mon, 18 May 2009 07:04:02 -0700, ReportSmith wrote:

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.