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

[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

 

[Marshall Barton]

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.

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

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

 

[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.

 

[Marshall Barton]

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.

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]