Prompted for parameter on OpenReport

[Aaron]

Hello,

I'm trying to limit a report to a list of parameters that I pass in.
The report and the underlying query both use the column FirstName.
Whenever I try to limit the FirstName in via the "Where" clause, I get
prompted to input the FirstName parameter, even though I'm passing it
in via OpenReport

The syntax I tried using:

DoCmd.OpenRepor t "ShowEmployees" , acViewNormal,
,"([employee].[firstname] = ""John"")"

-- And --

DoCmd.OpenRepor t "ShowEmployees" , acViewNormal,
,"([employee].[firstname] = 'John')"

My sql query is
select firstname, lastname from employee

I've poured through a ton of newsgroup postings related to OpenReport
and haven't come up with a solution. I have tried many variations on
the syntax, and tried using filters instead, but I keep getting
prompted to give a FirstName. Even when I do give a valid FirstName,
the query isn't limited and I keep getting a complete list of
employees.

Is there some other area in Access that I should be troubleshooting
besides the OpenReport syntax?

Thanks in advance for any help you can provide.

Aaron

[Pavel Romashkin]

My guess is, the filter you are trying to pass to the OpenReport is not
based on the data source for the report, as it is apparently a query,
not the Employees table.
Why not place the criteria into the source query in the first place:

select firstname, lastname from employee where firstname = "John"

and use this SQL as the report's data source?
If you have to define the filter at runtime, you have options: in 2002,
you can use OpenArgs; in earlier you have to use the report's Open event
to obtain the filtering criteria.
Pavel

Aaron wrote:

Hello,

I'm trying to limit a report to a list of parameters that I pass in.
The report and the underlying query both use the column FirstName.
Whenever I try to limit the FirstName in via the "Where" clause, I get
prompted to input the FirstName parameter, even though I'm passing it
in via OpenReport

The syntax I tried using:

DoCmd.OpenRepor t "ShowEmployees" , acViewNormal,
,"([employee].[firstname] = ""John"")"

-- And --

DoCmd.OpenRepor t "ShowEmployees" , acViewNormal,
,"([employee].[firstname] = 'John')"

My sql query is
select firstname, lastname from employee

I've poured through a ton of newsgroup postings related to OpenReport
and haven't come up with a solution. I have tried many variations on
the syntax, and tried using filters instead, but I keep getting
prompted to give a FirstName. Even when I do give a valid FirstName,
the query isn't limited and I keep getting a complete list of
employees.

Is there some other area in Access that I should be troubleshooting
besides the OpenReport syntax?

Thanks in advance for any help you can provide.

Aaron

[Fredg]

Aaron,

DoCmd.OpenRepor t "ShowEmployees" , acViewNormal, ,"[firstname] = 'John'"

Why are you hard coding this?
Do you intend to recode each time you want a report with a different first
name?

The simplest way is to just change the query:
SELECT Employee.FirstN ame, Employee.LastNa me
FROM Employee
WHERE Employee.FirstN ame =[Enter First Name];

Then run the report.

You'll be prompted for the name when you open the report.

If you have a form displaying the employee records, then you would use:
Docmd.OpenRepor t "ShowEmployees" , acViewNormal, , "[FirstName] = '" &
Me![FirstName] & "'"

Whatever the [FirstName] is that is on the record displayed on the form will
be the one filtered in the report.
John Smith, John Anderson, John Jones, etc.
In this case do not use the query criteria.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.
"Aaron" <de************ ****@hotmail.co m> wrote in message
news:c5******** *************** ***@posting.goo gle.com...

Hello,

I'm trying to limit a report to a list of parameters that I pass in.
The report and the underlying query both use the column FirstName.
Whenever I try to limit the FirstName in via the "Where" clause, I get
prompted to input the FirstName parameter, even though I'm passing it
in via OpenReport

The syntax I tried using:

DoCmd.OpenRepor t "ShowEmployees" , acViewNormal,
,"([employee].[firstname] = ""John"")"

-- And --

DoCmd.OpenRepor t "ShowEmployees" , acViewNormal,
,"([employee].[firstname] = 'John')"

My sql query is
select firstname, lastname from employee

I've poured through a ton of newsgroup postings related to OpenReport
and haven't come up with a solution. I have tried many variations on
the syntax, and tried using filters instead, but I keep getting
prompted to give a FirstName. Even when I do give a valid FirstName,
the query isn't limited and I keep getting a complete list of
employees.

Is there some other area in Access that I should be troubleshooting
besides the OpenReport syntax?

Thanks in advance for any help you can provide.

Aaron

[PaulT]

how or where did you try to run this from? I generically recreated
what you say you did, and I didn't have any trouble. Here is what I
did:
1. I created a table 'zz' with an Id, fname, & lname fields. I entered
ONE entry: fname = 'scott', lname = 'bradley'.

2. I created a query 'qz' to queries all 3 fields from 'zz'
3. I created a report 'showEmployees' based on 'qz'
4. I then created in funtion in module1, called 'showReport' [this is
where I'm wondering how/where you are calling 'OpenReport']

5. the function has this one statement:
doCmd.OpenRepor t "showEmployees" , acViewPreview,, "([zz].[fname] =
""scott"")"
6. I then created a macro 'a' with only one action, "RunCode" with the
function name showReport().
7. after executing the macro, I got a preview of the report with all
three fields shown: id = 1, fname=scott, lname=bradley

So, we probably need more info about how you are executing
'OpenReport'.

-PT

de************* ***@hotmail.com (Aaron) wrote in message news:<c5******* *************** ****@posting.go ogle.com>...

Hello,

I'm trying to limit a report to a list of parameters that I pass in.
The report and the underlying query both use the column FirstName.
Whenever I try to limit the FirstName in via the "Where" clause, I get
prompted to input the FirstName parameter, even though I'm passing it
in via OpenReport

The syntax I tried using:

DoCmd.OpenRepor t "ShowEmployees" , acViewNormal,
,"([employee].[firstname] = ""John"")"

-- And --

DoCmd.OpenRepor t "ShowEmployees" , acViewNormal,
,"([employee].[firstname] = 'John')"

My sql query is
select firstname, lastname from employee

I've poured through a ton of newsgroup postings related to OpenReport
and haven't come up with a solution. I have tried many variations on
the syntax, and tried using filters instead, but I keep getting
prompted to give a FirstName. Even when I do give a valid FirstName,
the query isn't limited and I keep getting a complete list of
employees.

Is there some other area in Access that I should be troubleshooting
besides the OpenReport syntax?

Thanks in advance for any help you can provide.

Aaron