Acc2007 throws Err3194 when Report RS contains valid PARAMETERS cl

[Michael Matuszak]

There is a problem that I have discovered with Access 2007 that I have only
found reported once – at UtterAccess – but it was somewhat dismissed there by
an Access MVP. If you try to open a report that has a SQL statement with a
PARAMETERS clause as the Record Source, Access 2007 throws Error 3194. If you
use a named query with the exact same SQL for the Record Source, no error.
This was not an issue in Access 2003 or earlier.

This will be a serious problem for my company’s clients, since many of our
standard reports need to have a SQL statement with a PARAMETERS clause in the
Record Source.

To see a simple example of this, download this file:

http://home.comcast.net/~mmatuszak/ParameterClauseThrowsError3194.zip

 

[AnandaSim]

an Access MVP. If you try to open a report that has a SQL statement with a
PARAMETERS clause as the Record Source, Access 2007 throws Error 3194. If you
use a named query with the exact same SQL for the Record Source, no error.

I tested it driving it interactively not via VBA call. I get an error
dialog saying that the PARAMETERS has a syntax problem.

If I remove the formal PARAMETERS declaration but still use the
criteria, it works.

This was not an issue in Access 2003 or earlier.

Didn't try it on an earlier Access - not on this machine I am using
right now. Yet.

This will be a serious problem for my company's clients, since many of our
standard reports need to have a SQL statement with a PARAMETERS clause in the
Record Source.

Have you tried using a Control on a Form to prompt the user for the
parameter? More often, I use a form as a criteria dialog.

HTH

Ananda

 

[Michael Matuszak]

If I remove the formal PARAMETERS declaration but still use the
criteria, it works.

My example was a simple one intended only to demonstrate that something has
clearly changed in the functionality of Access. I don't know the precise
history of why such clauses were added to our standard reports, but it is
unlikely they were added without addressing a specific problem that arose.
Most of our reports do not have PARAMETERS clauses.

Have you tried using a Control on a Form to prompt the user for the
parameter? More often, I use a form as a criteria dialog.

We do that in a limted way. However, a key principle in software development
is never to take away previously existing functionality. That is what has
happened here, with the result that we now must scramble to find alternative
ways to accomplish things that were much more easily accomplished with
previous functionality. I find that unacceptable. I do, however, appreciate
your suggestions.

HTH

Ananda

Michael