Modification of solution by Sal Ricciardi

[Moche]

The solution provided by Sal Ricciardi is what I need help in modifying. He
outlines a way to create a form that can be used to filter a report. In his
example he uses a combo box and two text boxes. My combo box data source is a
select statement that pulls all DISTINCT values from a table from which the
user can make a selection to filter the report. Now this is what I need help
with. I need a way to set the chose to ALL records by default. So, there will
be by default in the combo box the word "ALL" (I don't know how do this
either) and the user can click on the "Run report" button and get all the
records in the table. I should mention that I have three combo boxes on my
form. So, to summarize I want all records returned by default when the user
clicks on the "Run report" button and the three combo boxes should also have
the word "ALL" in them indicating their default value. The URL for Sal
Ricciardi's solution can be found at
http://office.microsoft.com/en-us/assistance/HA011170771033.aspx

 

[Duane Hookom]

You can add "All" to your combo box by setting the Row Source to something
like:

SELECT Products.ProductID, Products.ProductName
FROM Products
UNION
SELECT -1,"All Products"
FROM Products;

Your criteria would then check to see if the value of the combo box
equals -1. If so, compare the field value to itself:

IIf([Forms]![Sales By Category]![cboProductID]=-1, [ProductID],
[Forms]![Sales By Category]![cboProductID])

While Sal's sample is good, it lacks a good naming convention which is
essential to good application development.

 

[Moche]

I have followed your suggestion changeing the SQL as follows:

*/Original Code
SELECT DISTINCT [All Bids Data].[Action plan]
FROM [All Bids Data];


*// Modified Code
SELECT DISTINCT [All Bids Data].[Action plan]
FROM [All Bids Data]
UNION
SELECT -1, "All Plans"
FROM [All Bids Data]

I get this error message:
The number of columns in the two selected tables or queries of a union
query do not match. I'm not sure how to resolve this error, can you help?

You can add "All" to your combo box by setting the Row Source to something
like:

SELECT Products.ProductID, Products.ProductName
FROM Products
UNION
SELECT -1,"All Products"
FROM Products;

Your criteria would then check to see if the value of the combo box
equals -1. If so, compare the field value to itself:

IIf([Forms]![Sales By Category]![cboProductID]=-1, [ProductID],
[Forms]![Sales By Category]![cboProductID])

While Sal's sample is good, it lacks a good naming convention which is
essential to good application development.

--
Duane Hookom
MS Access MVP
--

The solution provided by Sal Ricciardi is what I need help in modifying.
He
outlines a way to create a form that can be used to filter a report. In
his
example he uses a combo box and two text boxes. My combo box data source
is a
select statement that pulls all DISTINCT values from a table from which
the
user can make a selection to filter the report. Now this is what I need
help
with. I need a way to set the chose to ALL records by default. So, there
will
be by default in the combo box the word "ALL" (I don't know how do this
either) and the user can click on the "Run report" button and get all the
records in the table. I should mention that I have three combo boxes on my
form. So, to summarize I want all records returned by default when the
user
clicks on the "Run report" button and the three combo boxes should also
have
the word "ALL" in them indicating their default value. The URL for Sal
Ricciardi's solution can be found at
http://office.microsoft.com/en-us/assistance/HA011170771033.aspx

 

[Moche]

I have followed your suggestion changeing the SQL as follows:

*/Original Code
SELECT DISTINCT [All Bids Data].[Action plan]
FROM [All Bids Data];


*// Modified Code
SELECT DISTINCT [All Bids Data].[Action plan]
FROM [All Bids Data]
UNION
SELECT -1, "All Plans"
FROM [All Bids Data]

I get this error message:
The number of columns in the two selected tables or queries of a union
query do not match. I not sure how to resolve this error, can you help?

You can add "All" to your combo box by setting the Row Source to something
like:

SELECT Products.ProductID, Products.ProductName
FROM Products
UNION
SELECT -1,"All Products"
FROM Products;

Your criteria would then check to see if the value of the combo box
equals -1. If so, compare the field value to itself:

IIf([Forms]![Sales By Category]![cboProductID]=-1, [ProductID],
[Forms]![Sales By Category]![cboProductID])

While Sal's sample is good, it lacks a good naming convention which is
essential to good application development.

--
Duane Hookom
MS Access MVP
--

The solution provided by Sal Ricciardi is what I need help in modifying.
He
outlines a way to create a form that can be used to filter a report. In
his
example he uses a combo box and two text boxes. My combo box data source
is a
select statement that pulls all DISTINCT values from a table from which
the
user can make a selection to filter the report. Now this is what I need
help
with. I need a way to set the chose to ALL records by default. So, there
will
be by default in the combo box the word "ALL" (I don't know how do this
either) and the user can click on the "Run report" button and get all the
records in the table. I should mention that I have three combo boxes on my
form. So, to summarize I want all records returned by default when the
user
clicks on the "Run report" button and the three combo boxes should also
have
the word "ALL" in them indicating their default value. The URL for Sal
Ricciardi's solution can be found at
http://office.microsoft.com/en-us/assistance/HA011170771033.aspx

 

[Duane Hookom]

My sample had two columns in each part of the select. You would need to use
something like:

SELECT [Action plan]
FROM [All Bids Data]
UNION
SELECT "All Plans"
FROM [All Bids Data];

IIf([Forms]![frmSelectPlans]![cboActionPlan]="All Plans", [Action Plan],
[Forms]![frmSelectPlans]![cboActionPlan])

--
Duane Hookom
MS Access MVP
--

I have followed your suggestion changeing the SQL as follows:

*/Original Code
SELECT DISTINCT [All Bids Data].[Action plan]
FROM [All Bids Data];


*// Modified Code
SELECT DISTINCT [All Bids Data].[Action plan]
FROM [All Bids Data]
UNION
SELECT -1, "All Plans"
FROM [All Bids Data]

I get this error message:
The number of columns in the two selected tables or queries of a union
query do not match. I not sure how to resolve this error, can you help?

You can add "All" to your combo box by setting the Row Source to
something
like:

SELECT Products.ProductID, Products.ProductName
FROM Products
UNION
SELECT -1,"All Products"
FROM Products;

Your criteria would then check to see if the value of the combo box
equals -1. If so, compare the field value to itself:

IIf([Forms]![Sales By Category]![cboProductID]=-1, [ProductID],
[Forms]![Sales By Category]![cboProductID])

While Sal's sample is good, it lacks a good naming convention which is
essential to good application development.

--
Duane Hookom
MS Access MVP
--

The solution provided by Sal Ricciardi is what I need help in
modifying.
He
outlines a way to create a form that can be used to filter a report. In
his
example he uses a combo box and two text boxes. My combo box data
source
is a
select statement that pulls all DISTINCT values from a table from which
the
user can make a selection to filter the report. Now this is what I need
help
with. I need a way to set the chose to ALL records by default. So,
there
will
be by default in the combo box the word "ALL" (I don't know how do this
either) and the user can click on the "Run report" button and get all
the
records in the table. I should mention that I have three combo boxes on
my
form. So, to summarize I want all records returned by default when the
user
clicks on the "Run report" button and the three combo boxes should also
have
the word "ALL" in them indicating their default value. The URL for Sal
Ricciardi's solution can be found at
http://office.microsoft.com/en-us/assistance/HA011170771033.aspx