Query Filter Syntax

[Pete]

Background
I have a report that uses a combo box to filter the data. The user can
choose ‘All Reports’, ‘Blank’ (i.e. field has not been filled in), or choose
a specific Report. The query used to populate the combo is shown below with
an example of the data it returns. (Note: I use ANSI 92 syntax in
anticipation of a move to SQL Server)

SELECT "%" as iReportID, 0 as SortOrder, "(All Reports)" as cReport FROM
tlkpReport UNION SELECT "0" as iReportID, 0 as SortOrder, "(Blank)" as
cReport FROM tlkpReport UNION SELECT tlkpReport.iReportID,
IIf(IsNull([nSortOrder]),99,[nSortOrder]) AS SortOrder, tlkpReport.cReport
FROM tlkpReport ORDER BY SortOrder, cReport;

iReportID SortOrder cReport
% 0 (All Reports)
0 0 (Blank)
1 1 Draft rep
2 2 Final rep
3 3 etc...

The relevant part of the WHERE clause in my report record source query is
shown below:

WHERE nz([tblStudy_Report].[iReportID],0) ALike
[Forms]![frmMyForm]![cboReport]

The above works fine.

Problem
However I have now been asked to add another option to the Report filter
combo “Draft and Final Reportsâ€.

It is straightforward enough to UNION another row into the combo record
source but, despite trying lots of different permutations have so far been
unable to find a combination of ‘iReportID’ value/Report Filter that will
work in every scenario (e.g. Can get ‘Draft and Final Report’ or selecting a
specific report to work, but ‘All’ does not).

Any help appreciated

Peter

 

[Marshall Barton]

Background
I have a report that uses a combo box to filter the data. The user can
choose ‘All Reports’, ‘Blank’ (i.e. field has not been filled in), or choose
a specific Report. The query used to populate the combo is shown below with
an example of the data it returns. (Note: I use ANSI 92 syntax in
anticipation of a move to SQL Server)

SELECT "%" as iReportID, 0 as SortOrder, "(All Reports)" as cReport FROM
tlkpReport UNION SELECT "0" as iReportID, 0 as SortOrder, "(Blank)" as
cReport FROM tlkpReport UNION SELECT tlkpReport.iReportID,
IIf(IsNull([nSortOrder]),99,[nSortOrder]) AS SortOrder, tlkpReport.cReport
FROM tlkpReport ORDER BY SortOrder, cReport;

iReportID SortOrder cReport
% 0 (All Reports)
0 0 (Blank)
1 1 Draft rep
2 2 Final rep
3 3 etc...

The relevant part of the WHERE clause in my report record source query is
shown below:

WHERE nz([tblStudy_Report].[iReportID],0) ALike
[Forms]![frmMyForm]![cboReport]

The above works fine.

Problem
However I have now been asked to add another option to the Report filter
combo “Draft and Final Reports”.

It is straightforward enough to UNION another row into the combo record
source but, despite trying lots of different permutations have so far been
unable to find a combination of ‘iReportID’ value/Report Filter that will
work in every scenario (e.g. Can get ‘Draft and Final Report’ or selecting a
specific report to work, but ‘All’ does not).

I think you can use:

[1 2] 1 Draft or Final rep

 

[Pete]

Hi Marsh

Thanks for your reply. I am not sure if you meant to put anything between
the numbers in the square brackets? I tried various things, such as OR, - .

In the end I have given up with this approach and instead dynamically change
the Report's record source query to a special one for this scenario. It is
not a 'neat' solution and I would have liked to have cracked the original
problem as users are bound to want other combinations in the future. It would
be much quicker and easier to just have to change the row source for the
filter combo than create additional queries and code these into the report ;-)

Regards

Peter
--
Peter Schmidt
Ross-on-Wye, UK

Background
I have a report that uses a combo box to filter the data. The user can
choose ‘All Reports’, ‘Blank’ (i.e. field has not been filled in), or choose
a specific Report. The query used to populate the combo is shown below with
an example of the data it returns. (Note: I use ANSI 92 syntax in
anticipation of a move to SQL Server)

SELECT "%" as iReportID, 0 as SortOrder, "(All Reports)" as cReport FROM
tlkpReport UNION SELECT "0" as iReportID, 0 as SortOrder, "(Blank)" as
cReport FROM tlkpReport UNION SELECT tlkpReport.iReportID,
IIf(IsNull([nSortOrder]),99,[nSortOrder]) AS SortOrder, tlkpReport.cReport
FROM tlkpReport ORDER BY SortOrder, cReport;

iReportID SortOrder cReport
% 0 (All Reports)
0 0 (Blank)
1 1 Draft rep
2 2 Final rep
3 3 etc...

The relevant part of the WHERE clause in my report record source query is
shown below:

WHERE nz([tblStudy_Report].[iReportID],0) ALike
[Forms]![frmMyForm]![cboReport]

The above works fine.

Problem
However I have now been asked to add another option to the Report filter
combo “Draft and Final Reportsâ€.

It is straightforward enough to UNION another row into the combo record
source but, despite trying lots of different permutations have so far been
unable to find a combination of ‘iReportID’ value/Report Filter that will
work in every scenario (e.g. Can get ‘Draft and Final Report’ or selecting a
specific report to work, but ‘All’ does not).

I think you can use:

[1 2] 1 Draft or Final rep

 

[Marshall Barton]

Thanks for your reply. I am not sure if you meant to put anything between
the numbers in the square brackets? I tried various things, such as OR, - .

No, nothing between the 12. Using a wildcard pattern of a
list of characters in square brackets will match only those
characters. In your case [12] will match either a 1 or a 2,
which I think are the IDs you wanted to find.

In the end I have given up with this approach and instead dynamically change
the Report's record source query to a special one for this scenario. It is
not a 'neat' solution and I would have liked to have cracked the original
problem as users are bound to want other combinations in the future. It would
be much quicker and easier to just have to change the row source for the
filter combo than create additional queries and code these into the report ;-)

The possible patterns you can specify using the wildcard
characters is extensive but limited. For a really general
situation where you don't know what the users will ask for
next, you will probably have to fall back to the record
source approach anyway.