filter adp report where record source uses aggregate functions

  • Thread starter Thread starter eddiec
  • Start date Start date
E

eddiec

hi everyone,

I have a report in an adp that uses aggregate functions in its record source
and I am trying to figure out how to filter the records displayed in the
report:

DoCmd.OpenReport with a SQL string for the where condition does not work.
The error returned is:
The column prefix dbo.mytable does not match with a table name or alias used
in the query

If I fire up SQL Query Analyzer and pop in the query with the where clause
at the end it returns the correct result set. Maybe the reason that this
does not work in Access is becasue DoCmd.OpenReport adds the where clause on
to the end of the query instead of putting it before the 'Group By'.

As I see it I have two options:

1) Simply program the entire report using VBA and not use the Access
reporting facility.
2) Retreive all the records from the database and then aggregate the
variables within the report. This is messy because you would generate a
large amount of network traffic (the source table has approx 5,000,000
records)

Help!

TIA

eddiec :-)
 
hi Arvin

Thank you for the tip. I put the code in the OnOpen event as per KB
http://support.microsoft.com/default.aspx?kbid=281998&product=acc2002

I no longer get the error that the table does not exist when passing the
openargs to the report but the results are still not filtered when passing
an openargs where condition. The report now displays a summary of the data
in the entire table. Two arguments are passed to the report so I thought
maybe this is the problem but I tried passing one argument and still
received the same result.

I would much appreciate your further assistance in this matter

TIA

eddiec :-)
 
I managed it using a combination of your suggestion and by modifying the
connection string by pulling the opening values directly from the dialog box
that launches the report

TYVM

:-)
 
Back
Top