Return All Records

[WPW07]

Hello,

I have a report that is a bound to a query. In the query, there is a
field called [status]. In that field I reference a field on a report
criteria form by using the following: [Forms]![frmReportCriteria]!
[cboStatus]. The user selects the criteria from a combo box on the
form and clicks the preview button.

I would like to modify this so that if the user doesn't select
anything from the list and clicks the preview button, all records will
be returned on the report. Right now, no records get returned.

Thanks for your help...

 

[Dirk Goldgar]

Hello,

I have a report that is a bound to a query. In the query, there is a
field called [status]. In that field I reference a field on a report
criteria form by using the following: [Forms]![frmReportCriteria]!
[cboStatus]. The user selects the criteria from a combo box on the
form and clicks the preview button.

I would like to modify this so that if the user doesn't select
anything from the list and clicks the preview button, all records will
be returned on the report. Right now, no records get returned.

Thanks for your help...

On the Criteria line of the query, under the [status] field, put

=[Forms]![frmReportCriteria]![cboStatus] Or
[Forms]![frmReportCriteria]![cboStatus] Is Null

Note: that will have been wrapped by the newsreader, but it should all be
entered on one line. Also, if you save the query and open it again, it will
probably have been reformatted to have
[Forms]![frmReportCriteria]![cboStatus] as an extra column with the Show box
unchecked and a criterion of Is Null.

 

[Jim Bunton]

On the combo add an extra row to the query either by adding " All" to your
list if statuses or if that's not convenient use a UNION query something
like
select status from mytable
UNION
select " All" as Status From Mytable
ORDER BY Status

then on the click preview button write code the click event to either
open an amost identical report but bound to a query that retrieves all
status values
or
rewrites the query that the same report's bound to before you opens it.

- actually you can do it all on the combo click event if it's convenent

Note the " All" has s apace before it as a trick to get it to list first
alphabetically on an order by stats line even if there's a statut "Aaa"
space comes before A alphabetically!

Jim Bunton

 

[WPW07]

Hello,

I have a report that is a bound to a query. In the query, there is a
field called [status]. In that field I reference a field on a report
criteria form by using the following: [Forms]![frmReportCriteria]!
[cboStatus]. The user selects the criteria from a combo box on the
form and clicks the preview button.

I would like to modify this so that if the user doesn't select
anything from the list and clicks the preview button, all records will
be returned on the report. Right now, no records get returned.

Thanks for your help...

On the Criteria line of the query, under the [status] field, put
Thanks. That works great.

=[Forms]![frmReportCriteria]![cboStatus] Or
[Forms]![frmReportCriteria]![cboStatus] Is Null

Note: that will have been wrapped by the newsreader, but it should all be
entered on one line. Also, if you save the query and open it again, it will
probably have been reformatted to have
[Forms]![frmReportCriteria]![cboStatus] as an extra column with the Show box
unchecked and a criterion of Is Null.