Parameters Question

[knowshowrosegrows]

I have a query with two parameters. One is a date range and the other is the
region (Region 1 through Region5). They trigger this query in a param form.
In the drop down they use to choose what region they want to search by, I
want a option of all regions. I don't know how to give them that option in
the drop down or in the SQL of the query.

The current SQL language follows:

SELECT qryEventsInfo.Reg, qryEventsInfo.Agency, qryEventsInfo.Type,
qryEventsInfo.EventDate, qryEventsInfo.StartTime, qryEventsInfo.StaffName,
qryEventsInfo.EventDescription, qryEventsInfo.ReportSubmitted,
qryEventsInfo.Notes
FROM qryEventsInfo
WHERE (((qryEventsInfo.Reg)=[Enter Region]) AND ((qryEventsInfo.EventDate)
Between [Starting Date] And [Ending Date]));

 

[Ken Snell \(MVP\)]

Assuming that the text "All Regions" is what you let your users select in
the combo box for all regions:

SELECT qryEventsInfo.Reg, qryEventsInfo.Agency, qryEventsInfo.Type,
qryEventsInfo.EventDate, qryEventsInfo.StartTime, qryEventsInfo.StaffName,
qryEventsInfo.EventDescription, qryEventsInfo.ReportSubmitted,
qryEventsInfo.Notes
FROM qryEventsInfo
WHERE (((qryEventsInfo.Reg)=[Enter Region] OR
([Enter Region]) = "All Regions") AND ((qryEventsInfo.EventDate)
Between [Starting Date] And [Ending Date]));


In your combo box RowSource query, you'd use something like this:

SELECT Region
FROM Yourtable
UNION ALL
SELECT DISTINCT "All Regions" As Region
FROM Yourtable;

 

[knowshowrosegrows]

How simple.

Thanks for your smarts.
--
Thanks

You all are teaching me so much

Assuming that the text "All Regions" is what you let your users select in
the combo box for all regions:

SELECT qryEventsInfo.Reg, qryEventsInfo.Agency, qryEventsInfo.Type,
qryEventsInfo.EventDate, qryEventsInfo.StartTime, qryEventsInfo.StaffName,
qryEventsInfo.EventDescription, qryEventsInfo.ReportSubmitted,
qryEventsInfo.Notes
FROM qryEventsInfo
WHERE (((qryEventsInfo.Reg)=[Enter Region] OR
([Enter Region]) = "All Regions") AND ((qryEventsInfo.EventDate)
Between [Starting Date] And [Ending Date]));


In your combo box RowSource query, you'd use something like this:

SELECT Region
FROM Yourtable
UNION ALL
SELECT DISTINCT "All Regions" As Region
FROM Yourtable;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have a query with two parameters. One is a date range and the other is
the
region (Region 1 through Region5). They trigger this query in a param
form.
In the drop down they use to choose what region they want to search by, I
want a option of all regions. I don't know how to give them that option
in
the drop down or in the SQL of the query.

The current SQL language follows:

SELECT qryEventsInfo.Reg, qryEventsInfo.Agency, qryEventsInfo.Type,
qryEventsInfo.EventDate, qryEventsInfo.StartTime, qryEventsInfo.StaffName,
qryEventsInfo.EventDescription, qryEventsInfo.ReportSubmitted,
qryEventsInfo.Notes
FROM qryEventsInfo
WHERE (((qryEventsInfo.Reg)=[Enter Region]) AND ((qryEventsInfo.EventDate)
Between [Starting Date] And [Ending Date]));