Choose "ALL" in Combo Box

[knowshowrosegrows]

I have a parameter form that is attached to a query. I want people to choose
the date range of the parameters and the "region." I only have 3 possible
regions. I want them to have a dropdown that offeres "All Regions," "Region
I," "Region II" or "Region III." I don't know how to give the region
dropdown the "ALL" option.

 

[BruceM]

If th combo box row source is a value list all you need to do is add
something like "(All)" to the list. If it is a table/query you can use a
union query to add (All). This llnk has more information:
http://www.mvps.org/access/forms/frm0043.htm

The next thing is to have the code do something specific when (All) is
selected.

 

[Linq Adams via AccessMonster.com]

Replace your combobox RowSource with this

SELECT YourField FROM YourTable
UNION SELECT "All Regions" FROM YourTable;

filling in your actual field and table names.

 

[knowshowrosegrows]

OK, I have gotten myself confused about this All option and can't make it work.

My parameter form has a StartDate and an EndDate textbox. It also has a
ChooseType combo box.

The ChooseType combo box has the following in the row source:
SELECT "*" AS Type_ID, "ALL" AS Type, 0 AS SortOrder FROM tblType UNION
SELECT Type_ID, Type, 1 FROM tblType ORDER BY SortOrder, Type;

The column count is 2 and the bound column is 2.

the column widths is : 0";4"

The CmdRunRpt button has the following in the OnClick Code:
DoCmd.OpenReport "rptEventInfoByType/Date", acPreview, , "Type LIKE '" &
Me.ChooseType & "*'AND [EventDate] Between #" & Me.[StartDate] & "# AND #" &
Me.[EndDate] & "#"

When I choose one of the types and a date range I get a great report, but
when I choose All I get nothing in the report.

Can anyone help?

 

[Douglas J. Steele]

If Me.ChooseType = "All" Then
DoCmd.OpenReport "rptEventInfoByType/Date", acPreview, , _
"[EventDate] Between #" & Me.[StartDate] & "# AND #" & _
Me.[EndDate] & "#"
Else
DoCmd.OpenReport "rptEventInfoByType/Date", acPreview, , _
"Type LIKE '" & Me.ChooseType & "*'AND [EventDate] Between #" & _
Me.[StartDate] & "# AND #" & Me.[EndDate] & "#"
End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK, I have gotten myself confused about this All option and can't make it
work.

My parameter form has a StartDate and an EndDate textbox. It also has a
ChooseType combo box.

The ChooseType combo box has the following in the row source:
SELECT "*" AS Type_ID, "ALL" AS Type, 0 AS SortOrder FROM tblType UNION
SELECT Type_ID, Type, 1 FROM tblType ORDER BY SortOrder, Type;

The column count is 2 and the bound column is 2.

the column widths is : 0";4"

The CmdRunRpt button has the following in the OnClick Code:
DoCmd.OpenReport "rptEventInfoByType/Date", acPreview, , "Type LIKE '" &
Me.ChooseType & "*'AND [EventDate] Between #" & Me.[StartDate] & "# AND #"
&
Me.[EndDate] & "#"

When I choose one of the types and a date range I get a great report, but
when I choose All I get nothing in the report.

Can anyone help?
--
Thanks

You all are teaching me so much

Replace your combobox RowSource with this

SELECT YourField FROM YourTable
UNION SELECT "All Regions" FROM YourTable;

filling in your actual field and table names.

 

[knowshowrosegrows]

Thanks - it works. I am putting your suggestion in my snippets folder right
now.
--
Thanks

You all are teaching me so much

If Me.ChooseType = "All" Then
DoCmd.OpenReport "rptEventInfoByType/Date", acPreview, , _
"[EventDate] Between #" & Me.[StartDate] & "# AND #" & _
Me.[EndDate] & "#"
Else
DoCmd.OpenReport "rptEventInfoByType/Date", acPreview, , _
"Type LIKE '" & Me.ChooseType & "*'AND [EventDate] Between #" & _
Me.[StartDate] & "# AND #" & Me.[EndDate] & "#"
End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK, I have gotten myself confused about this All option and can't make it
work.

My parameter form has a StartDate and an EndDate textbox. It also has a
ChooseType combo box.

The ChooseType combo box has the following in the row source:
SELECT "*" AS Type_ID, "ALL" AS Type, 0 AS SortOrder FROM tblType UNION
SELECT Type_ID, Type, 1 FROM tblType ORDER BY SortOrder, Type;

The column count is 2 and the bound column is 2.

the column widths is : 0";4"

The CmdRunRpt button has the following in the OnClick Code:
DoCmd.OpenReport "rptEventInfoByType/Date", acPreview, , "Type LIKE '" &
Me.ChooseType & "*'AND [EventDate] Between #" & Me.[StartDate] & "# AND #"
&
Me.[EndDate] & "#"

When I choose one of the types and a date range I get a great report, but
when I choose All I get nothing in the report.

Can anyone help?
--
Thanks

You all are teaching me so much

Replace your combobox RowSource with this

SELECT YourField FROM YourTable
UNION SELECT "All Regions" FROM YourTable;

filling in your actual field and table names.