Select All from drop down

[Guest]

I created a form where a drop down list appears when you open a report that
lets you select from 9 different regions. Would like to add an ALL option on
the drop down list that will give the total of all 9 regions.

 

[fredg]

I created a form where a drop down list appears when you open a report that
lets you select from 9 different regions. Would like to add an ALL option on
the drop down list that will give the total of all 9 regions.

First you need to add the All option to the combo box.
As ComboBox Row source, adapt the following:

Select TableName.[Region] From TableName
Union Select "All" from TableName
Order By [Region]

Substitute your table and field names.

Then create q query that is the report's Record Source.
As criteria on thee [Region] column, write:

Like IIf(forms!FormName!ComboName = "All","*",
forms!FormName!ComboName)

 

[Guest]

I entered as you requested and I get an error message that states "The number
of columns in the two selected tables or queries of a union query do not
match". I have a Regions Table that just lists the 9 regions that I added an
ALL to the region list(2 columns). The Vendor Master Table that has the
sales data on only lists the warehouse location for each row of data, not the
region(21 columns). I have a Region List Table that has the warehouse
location in one row and the region of that location in the second row(3
columns). On my query I have the Vendor Master Table, the Region List Table,
and the Region Table linked together and use the region from the Region List
in the query.

I created a form where a drop down list appears when you open a report that
lets you select from 9 different regions. Would like to add an ALL option on
the drop down list that will give the total of all 9 regions.

First you need to add the All option to the combo box.
As ComboBox Row source, adapt the following:

Select TableName.[Region] From TableName
Union Select "All" from TableName
Order By [Region]

Substitute your table and field names.

Then create q query that is the report's Record Source.
As criteria on thee [Region] column, write:

Like IIf(forms!FormName!ComboName = "All","*",
forms!FormName!ComboName)

 

[fredg]

I entered as you requested and I get an error message that states "The number
of columns in the two selected tables or queries of a union query do not
match". I have a Regions Table that just lists the 9 regions that I added an
ALL to the region list(2 columns). The Vendor Master Table that has the
sales data on only lists the warehouse location for each row of data, not the
region(21 columns). I have a Region List Table that has the warehouse
location in one row and the region of that location in the second row(3
columns). On my query I have the Vendor Master Table, the Region List Table,
and the Region Table linked together and use the region from the Region List
in the query.

I created a form where a drop down list appears when you open a report that
lets you select from 9 different regions. Would like to add an ALL option on
the drop down list that will give the total of all 9 regions.

First you need to add the All option to the combo box.
As ComboBox Row source, adapt the following:

Select TableName.[Region] From TableName
Union Select "All" from TableName
Order By [Region]

Substitute your table and field names.

Then create q query that is the report's Record Source.
As criteria on thee [Region] column, write:

Like IIf(forms!FormName!ComboName = "All","*",
forms!FormName!ComboName)

I am completely confused.

It always helps to post existing code, so that anyone who tries to
help will see just what it is your are trying to do.

You want to add the word 'All' to a combo box drop down list.
You do not need to add 'All' to the Region list Table.
You need to add it to the combo box row source only.

In my example, in my previous post, the Combo box row source was one
column, so all you add is the word "All".

If the combo box row source is 2 columns, you have to add an
additional value besides the word "All", so that the Union Select is
also 2 columns.
If the combo box row source is in 3 columns, you need to add 2
additional values beside the word "All", etc.

For example:
SELECT YourTable.[Field1], YourTable.[Field2] , YourTable.[Field3]
FROM YourTable
Union Select Null,Null,"All" from YourTable
Order by [Field3];

The above assumes you wish the word "All" to appear in the 3rd column.

If you need to give the "All" row an ID value as well, assuming the
1st column is an ID column, you can use:

SELECT YourTable.[Field1], YourTable.[Field2] , YourTable.[Field3]
FROM YourTable
Union Select 9999,Null,"All" from YourTable
Order by [Field3];

which gives the All row an ID value of 9999.

If you still have difficulty, please post the exact SQL of your combo
box row source THAT WAS WORKING before you attempted to add the word
"All".