Creating parameter report in Access 2003 project (ADP)

[Andy]

Hi guys

I having a problem creating a report in Access 2003 project talking to
a SQL database through and ODBC connect.

After hours of trying things from Access Help, MSDN and Google I still
can't get it working.

I have a query defined (view) and want the end user to put in a start
date and end date to filter a report.

I tried creating a parameter on the Criteria line of the query but got
this error:

'[Start Date:]' in expression is not part of the query.

Alternatively, I tried this line the query:

'WHERE (dbo.Case.Repor tDate BETWEEN @StartDate AND @EndDate)'

and got this error 'ADO error: Must declare the variable '@StartDate'

I'm stumped...any ideas please.

Thanks....Andre w

[Tom van Stiphout]

On Thu, 14 Jun 2007 00:05:16 -0700, Andy <an******@hotma il.comwrote:

Turn the view into a stored procedure that takes two input parameters,
and set those values in the InputParameters property of the report.

Btw, if you're using an Access Project (adp), you're not using ODBC.

-Tom.

>Hi guys

I having a problem creating a report in Access 2003 project talking to
a SQL database through and ODBC connect.

After hours of trying things from Access Help, MSDN and Google I still
can't get it working.

I have a query defined (view) and want the end user to put in a start
date and end date to filter a report.

I tried creating a parameter on the Criteria line of the query but got
this error:

'[Start Date:]' in expression is not part of the query.

Alternativel y, I tried this line the query:

'WHERE (dbo.Case.Repor tDate BETWEEN @StartDate AND @EndDate)'

and got this error 'ADO error: Must declare the variable '@StartDate'

I'm stumped...any ideas please.

Thanks....Andr ew

[Andy]

Thanks Tom

I was trying to avoid using stored procedures because I don't have
permission add stored procedures directly to the database (have to go
through a labourious procedure through IT.)...but I can create views.
My reading of parameter queries/views was that I should have been able
to create these from the Access 2003 interface...whi ch is where I got
the errors?

Another thought, can I create a stored procedure where I pass a table
name and date parameters to return a recordset from a chosen table.
In this way I could then use that stored procedure as the basis of any
report (access front end - SQL backend, relatively few records <
1000)??

thanks for your response....And y

P.S. I got confused...I am using a DSN...not ODBC.
On Jun 15, 12:25 am, Tom van Stiphout <no.spam.tom7.. [e-mail]:

On Thu, 14 Jun 2007 00:05:16 -0700, Andy <anzac...@hotma il.comwrote:

Turn the view into a stored procedure that takes two input parameters,
and set those values in the InputParameters property of the report.

Btw, if you're using an Access Project (adp), you're not using ODBC.

-Tom.

Hi guys

I having a problem creating a report in Access 2003 project talking to
a SQL database through and ODBC connect.

After hours of trying things from Access Help, MSDN and Google I still
can't get it working.

I have a query defined (view) and want the end user to put in a start
date and end date to filter a report.

I tried creating a parameter on the Criteria line of the query but got
this error:

'[Start Date:]' in expression is not part of the query.

Alternatively, I tried this line the query:

'WHERE (dbo.Case.Repor tDate BETWEEN @StartDate AND @EndDate)'

and got this error 'ADO error: Must declare the variable '@StartDate'

I'm stumped...any ideas please.

Thanks....Andre w- Hide quoted text -

- Show quoted text -