By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,254 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

filter adp report where record source uses aggregate functions?

P: n/a
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 :-)

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
If you post the actual code, that would help debug.

However, I suggest using a stored procedure as your Record Source and
passing the needed parameters in. It's fast, powerful, and any valid
SQL statement will work.

"eddiec" <ch***@netspace.net.au> wrote in message news:<40********@news.iprimus.com.au>...
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 :-)

Nov 13 '05 #2

P: n/a
On Jul 12 2004, 09:01 am, Chuck Grimsby
<c.*******@worldnet.att.net.invalid> wrote in
news:mo********************************@4ax.com:
Remove the "dbo." stuff. Both QA and Access will occasionally put
those in, but usually you don't need them.


Incidentally, when you bind a disconnected recordset based on a stored
procedure to a form, and the stored procedure is called with the dbo.
prefix, which supposedly improves performance, it confuses Access so much
that filters stop working. Should have mentioned that in my last post.

--
remove a 9 to reply by email
Nov 13 '05 #3

P: n/a
"eddiec" <ch***@netspace.net.au> wrote in
news:40********@news.iprimus.com.au:
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 :-)


Perhaps I am not understanding; why not use the report's InputParameters
property?

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.