WhereCondition in DoCmd.OpenReport does not work for an Aggregate Query

[david.malcolm]

Hi

I have a query about a query (!) which simply calculates a total and
works fine as the RecordSource for a report.
SELECT Count(lngRequestId) AS CountOflngRequestId
FROM tblRequests
WHERE (((dtmRequestRequestedDate) Between #01/01/2007# And
#01/31/2007#) AND (([lngRequestI]+[lngRequestIC]+[lngRequestVG]+
[lngRequestVC]+[lngRequestEG]+[lngRequestEC]+[lngRequestVP]+
[lngRequestCR])=0));

I wish to set this where condition when I run the report so I have
changed the query so there is no Where clause and then constructed the
Where clause and tried to use it with the WhereCondition of
DoCmd.OpenReport
The Query is now..
SELECT Count(lngRequestId) AS CountOflngRequestId
FROM tblRequests;

However when I try to set the WhereCondition clause using
DoCmd.OpenReport "rptName", acViewPreview ,, strSQL

I get prompted for all the fields in my Where condition as if they do
not exist in the database and my report does not run. My
WhereCondition strSQL is constructed correctly as far as I can tell...
strSQL = "(((dtmRequestRequestedDate) Between #01/01/2007# And
#01/31/2007#) AND "
strSQL = strSQL & "(([lngRequestI]+[lngRequestIC]+[lngRequestVG]+
[lngRequestVC]+[lngRequestEG]"
strSQL = strSQL & "+[lngRequestEC]+[lngRequestVP]+[lngRequestCR])=0))"

Is anyone aware of a problem with the WhereCondition when using
OpenReport. I can understand that the fields in my Where clause are
not mentioned as part of the Select statement, but the query works if
they are all together in the query itself, so why does it not work
when I separate them? I have tried this with simpler reports that do
not use an aggregate function and wonder if this is a restriction on
using the WhereCondition with the OpenReport command? Can someone
please advise if this can be done or if it is not possible at all?

Thanks

 

[Allen Browne]

Hi David

What's happening is that your query runs and sends its output to the report
(a single field in your example.) Your report then tries to apply criteria
on fields that the query is not sending to the report. And since the report
doesn't have those fields supplied to it from it source query, it assumes
they must be parameters, and asks you for them.

Hope that makes sense of the behaviour you are seeing. Now for some
workarounds.

The simplest suggestion is to open a form where you can enter the limiting
dates. Have the query read the dates from the form, by entering an
expression like this into the Criteria row in query design under the date
field:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
You type the correct dates into the form, and click the button to open the
report. The OpenReport does not need to use a WhereCondition, because the
query is reading the dates directly from the form. Especially since you are
working in a d/m/y country, you need to ensure Access understands the dates
correctly:
a) Set the Format property of these text boxes to Short Date.
b) Declare the parameters in the query.
Choose Parameters in query design, and enter 2 rows like this:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

That's the code free solution. If you don't mind writing code, there are
other solutions, such as using code in the Open event of the Report to build
a SQL string for the desired dates, and assigning it to the RecordSource of
the report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi

I have a query about a query (!) which simply calculates a total and
works fine as the RecordSource for a report.
SELECT Count(lngRequestId) AS CountOflngRequestId
FROM tblRequests
WHERE (((dtmRequestRequestedDate) Between #01/01/2007# And
#01/31/2007#) AND (([lngRequestI]+[lngRequestIC]+[lngRequestVG]+
[lngRequestVC]+[lngRequestEG]+[lngRequestEC]+[lngRequestVP]+
[lngRequestCR])=0));

I wish to set this where condition when I run the report so I have
changed the query so there is no Where clause and then constructed the
Where clause and tried to use it with the WhereCondition of
DoCmd.OpenReport
The Query is now..
SELECT Count(lngRequestId) AS CountOflngRequestId
FROM tblRequests;

However when I try to set the WhereCondition clause using
DoCmd.OpenReport "rptName", acViewPreview ,, strSQL

I get prompted for all the fields in my Where condition as if they do
not exist in the database and my report does not run. My
WhereCondition strSQL is constructed correctly as far as I can tell...
strSQL = "(((dtmRequestRequestedDate) Between #01/01/2007# And
#01/31/2007#) AND "
strSQL = strSQL & "(([lngRequestI]+[lngRequestIC]+[lngRequestVG]+
[lngRequestVC]+[lngRequestEG]"
strSQL = strSQL & "+[lngRequestEC]+[lngRequestVP]+[lngRequestCR])=0))"

Is anyone aware of a problem with the WhereCondition when using
OpenReport. I can understand that the fields in my Where clause are
not mentioned as part of the Select statement, but the query works if
they are all together in the query itself, so why does it not work
when I separate them? I have tried this with simpler reports that do
not use an aggregate function and wonder if this is a restriction on
using the WhereCondition with the OpenReport command? Can someone
please advise if this can be done or if it is not possible at all?

Thanks