Filter from Form Control Problem

[Pete]

I have a report that takes a filter condition from a combo box on a form. The
combo box is populated from a table using a UNION query that adds an ‘All’
option. The first column is bound to the control and is hidden.

NoteAlike and % are used in place of Like & * as the database is written
in SQL compatible mode in anticipation of a future upgrade)

Combo Box Source
SELECT DISTINCT "%" as iStudyStatusID, "(All Study Statuses)" as
cStudyStatus FROM tlkpStudyStatus
UNION
SELECT DISTINCT tlkpStudyStatus.iStudyStatusID, tlkpStudyStatus.cStudyStatus
FROM tlkpStudyStatus;

Query WHERE clause
…WHERE (tblStudy.iStudyStatusID ALike [Forms]![frmMyForm]![cboStudyStatus])…

This works fine. However, users have now requested the ability to select one
further option that combines 2 statuses. There is no need for a multiselect
list box. I have used the following to populate the combo box:

SELECT DISTINCT "%" as iStudyStatusID, "(All Study Statuses)" as
cStudyStatus FROM tlkpStudyStatus
UNION
SELECT DISTINCT "3 Or 4" as iStudyStatusID, "Reporting (Draft or Final)" as
cStudyStatus FROM tlkpStudyStatus
UNION
SELECT DISTINCT tlkpStudyStatus.iStudyStatusID, tlkpStudyStatus.cStudyStatus
FROM tlkpStudyStatus;

However, when the user selects "Reporting (Draft or Final)" the query does
not return any records. If you manually type ‘Alike 3 or 4’ into the query,
records are returned.

I guess it is because the control is returning a string value that needs to
be evaluated (I have tried various combinations using Eval and IIF
statements). I know I am being lazy doing it this way, but interested to know
if it can be done, as it is simpler than setting the report’s RecordSource
programmatically, as other combinations can be easily added without any need
for modifying code.

 

[John Spencer (MVP)]

If you are using alike as the criteria operator, then try using a range value.

SELECT DISTINCT "[34]" as iStudyStatusID, "Reporting (Draft or Final)" as
cStudyStatus FROM tlkpStudyStatus

By the way since you are using UNION (and not UNION ALL) you really don't need
to do SELECT DISTINCT since the UNION operator does a DISTINCT operation on the
entire set of records.

I'm not sure that the range value syntax will work with ALike. Try it and let
us know.

 

[Pete]

Thanks John "[3,4]" works. I like using this approach to filtering as it is
very simple as there is no need for extra queries or code in reports.

By the way, the reason for adding DISTINCT to the UNION query is to get
around an Access bug - when in SQL compatibilty mode, 'Auto Expand' fails to
work unless you add DISTINCT to the Row Source query!


--
Peter Schmidt
Ross-on-Wye, UK

If you are using alike as the criteria operator, then try using a range value.

SELECT DISTINCT "[34]" as iStudyStatusID, "Reporting (Draft or Final)" as
cStudyStatus FROM tlkpStudyStatus

By the way since you are using UNION (and not UNION ALL) you really don't need
to do SELECT DISTINCT since the UNION operator does a DISTINCT operation on the
entire set of records.

I'm not sure that the range value syntax will work with ALike. Try it and let
us know.

I have a report that takes a filter condition from a combo box on a form. The
combo box is populated from a table using a UNION query that adds an ‘All’
option. The first column is bound to the control and is hidden.

NoteAlike and % are used in place of Like & * as the database is written
in SQL compatible mode in anticipation of a future upgrade)

Combo Box Source
SELECT DISTINCT "%" as iStudyStatusID, "(All Study Statuses)" as
cStudyStatus FROM tlkpStudyStatus
UNION
SELECT DISTINCT tlkpStudyStatus.iStudyStatusID, tlkpStudyStatus.cStudyStatus
FROM tlkpStudyStatus;

Query WHERE clause
â€|WHERE (tblStudy.iStudyStatusID ALike [Forms]![frmMyForm]![cboStudyStatus])â€|

This works fine. However, users have now requested the ability to select one
further option that combines 2 statuses. There is no need for a multiselect
list box. I have used the following to populate the combo box:

SELECT DISTINCT "%" as iStudyStatusID, "(All Study Statuses)" as
cStudyStatus FROM tlkpStudyStatus
UNION
SELECT DISTINCT "3 Or 4" as iStudyStatusID, "Reporting (Draft or Final)" as
cStudyStatus FROM tlkpStudyStatus
UNION
SELECT DISTINCT tlkpStudyStatus.iStudyStatusID, tlkpStudyStatus.cStudyStatus
FROM tlkpStudyStatus;

However, when the user selects "Reporting (Draft or Final)" the query does
not return any records. If you manually type ‘Alike 3 or 4’ into the query,
records are returned.

I guess it is because the control is returning a string value that needs to
be evaluated (I have tried various combinations using Eval and IIF
statements). I know I am being lazy doing it this way, but interested to know
if it can be done, as it is simpler than setting the report’s RecordSource
programmatically, as other combinations can be easily added without any need
for modifying code.