REport based on a query and passing a where clause into the report

I

Irishmaninusa

Hi,

I am trying to call a report which is based on a query

SELECT * FROM tblContact

and in the access form I call the report with the following command
DoCmd.OpenReport "MailingList", acViewPreview, WHERECondition:=strWhere

where strWhere = responsibility IN ("Purchasing")

When I call this....I get prompted to type in a value for
responsibility...so I added the table name...and still got prompted....what
I am I doing wrong.

--
J.Daly
structure:interactive
Ph: 616-364-7423
Fx: 616-364-6941
http://www.structureinteractive.com
 
M

Marshall Barton

Irishmaninusa said:
Hi,

I am trying to call a report which is based on a query

SELECT * FROM tblContact

and in the access form I call the report with the following command
DoCmd.OpenReport "MailingList", acViewPreview, WHERECondition:=strWhere

where strWhere = responsibility IN ("Purchasing")

When I call this....I get prompted to type in a value for
responsibility...so I added the table name...and still got prompted....what
I am I doing wrong.
Click to expand...


Assuming that your code has
strWhere = "responsibility IN (""Purchasing"")"

I would suspect the spelling of the responsibility field's
name.
 
I

Irishmaninusa

Nope.....turns out....and I don't know why this is the case......but the
field has to appear in the report (whether or not you need it there).....and
then it works.
 
M

Marshall Barton

Irishmaninusa said:
Nope.....turns out....and I don't know why this is the case......but the
field has to appear in the report (whether or not you need it there).....and
then it works.
Click to expand...

This is a subtle issue. Access tries to optimize the
internal query it creates to drive the report (for sorting,
grouping, Sums, etc). As part of that optimization process,
it only retrieves fields that are referenced in controls.
--
Marsh
MVP [MS Access]


 
Top