Passing Query Parameters to a Report (.ADP Project)

  • Thread starter Thread starter Steve Roberts
  • Start date Start date
S

Steve Roberts

Access 2003 .ADP project on SQL 7.0

I have a stored proceedure that I am using for a report. In that proceedure
it prompts the user for the StartDate and the EndDate. Is it possible to
have the StartDate and EndDate print in the report header so the user knows
what the date range of the report is?

Here is the Stored Proceedure in case it is relivant.

SELECT dbo.TestSummary.ProjectNumber, dbo.TestSummary.Date
FROM dbo.TestSummary LEFT OUTER JOIN
dbo.tblReasons ON dbo.TestSummary.Reason =
dbo.tblReasons.ReasonID
WHERE (dbo.TestSummary.Date BETWEEN @StartDate AND @EndDate)

Thanks in advance

Steve
 
yes. Build an uncound text field similar to...

="Data Between " & [StartDate] & " and " & [EndDate]

Rick B



Access 2003 .ADP project on SQL 7.0

I have a stored proceedure that I am using for a report. In that proceedure
it prompts the user for the StartDate and the EndDate. Is it possible to
have the StartDate and EndDate print in the report header so the user knows
what the date range of the report is?

Here is the Stored Proceedure in case it is relivant.

SELECT dbo.TestSummary.ProjectNumber, dbo.TestSummary.Date
FROM dbo.TestSummary LEFT OUTER JOIN
dbo.tblReasons ON dbo.TestSummary.Reason =
dbo.tblReasons.ReasonID
WHERE (dbo.TestSummary.Date BETWEEN @StartDate AND @EndDate)

Thanks in advance

Steve
 
I am not sure how this is supposed to put the parameters that I input in the
Stored proceedure into fields on the report. When I try this it tells me
that the column does not exist.
 
It tells you the COLUMNS don't exist? What columns?

This should be an unbound text field.


I am not sure how this is supposed to put the parameters that I input in the
Stored proceedure into fields on the report. When I try this it tells me
that the column does not exist.
 
When I paste ="Data Between " & [StartDate] & " and " & [EndDate] into an
unbound text box, I receive the following error. Column(startdate) does not
exist in the appropriate rowset.

I assume that this is because the recordset doesn't actually contain a field
called StartDate. Maybe I am going about this all wrong? I tried placing
the Query directly into the record source of the report but I still get the
error.

Any ideas?
Thanks

Steve

Rick B said:
It tells you the COLUMNS don't exist? What columns?

This should be an unbound text field.


I am not sure how this is supposed to put the parameters that I input in the
Stored proceedure into fields on the report. When I try this it tells me
that the column does not exist.


Rick B said:
yes. Build an uncound text field similar to...

="Data Between " & [StartDate] & " and " & [EndDate]

Rick B



Access 2003 .ADP project on SQL 7.0

I have a stored proceedure that I am using for a report. In that proceedure
it prompts the user for the StartDate and the EndDate. Is it possible to
have the StartDate and EndDate print in the report header so the user knows
what the date range of the report is?

Here is the Stored Proceedure in case it is relivant.

SELECT dbo.TestSummary.ProjectNumber, dbo.TestSummary.Date
FROM dbo.TestSummary LEFT OUTER JOIN
dbo.tblReasons ON dbo.TestSummary.Reason =
dbo.tblReasons.ReasonID
WHERE (dbo.TestSummary.Date BETWEEN @StartDate AND @EndDate)

Thanks in advance

Steve
Click to expand...
Click to expand...
 
You stated that you are having the user enter a start and
end date so, to make this work, you will need to
replace "[startdate]" and "[enddate]" with whatever
variable you are using to store what the user enters for
start and end dates. And, if you are using a form to get
these dates, you would also need to include Forms!
-----Original Message-----
When I paste ="Data Between " & [StartDate] & " and " & [EndDate] into an
unbound text box, I receive the following error. Column (startdate) does not
exist in the appropriate rowset.

I assume that this is because the recordset doesn't actually contain a field
called StartDate. Maybe I am going about this all wrong? I tried placing
the Query directly into the record source of the report but I still get the
error.

Any ideas?
Thanks

Steve

It tells you the COLUMNS don't exist? What columns?

This should be an unbound text field.


I am not sure how this is supposed to put the
Click to expand...
parameters that I input in
the
Stored proceedure into fields on the report. When I try this it tells me
that the column does not exist.


yes. Build an uncound text field similar to...

="Data Between " & [StartDate] & " and " & [EndDate]

Rick B



Access 2003 .ADP project on SQL 7.0

I have a stored proceedure that I am using for a
Click to expand...
report. In that
proceedure
it prompts the user for the StartDate and the EndDate. Is it possible to
have the StartDate and EndDate print in the report
Click to expand...
header so the user
knows
what the date range of the report is?

Here is the Stored Proceedure in case it is relivant.

SELECT dbo.TestSummary.ProjectNumber, dbo.TestSummary.Date
FROM dbo.TestSummary LEFT OUTER JOIN
dbo.tblReasons ON dbo.TestSummary.Reason =
dbo.tblReasons.ReasonID
WHERE (dbo.TestSummary.Date BETWEEN @StartDate AND @EndDate)

Thanks in advance

Steve
Click to expand...
Click to expand...


.
Click to expand...
 
Hmm... May that is the problem.... I am using the following parameter query
for the report data:

Here is the Stored Proceedure in case it is relivant.

SELECT dbo.TestSummary.ProjectNumber, dbo.TestSummary.Date
FROM dbo.TestSummary LEFT OUTER JOIN
dbo.tblReasons ON dbo.TestSummary.Reason =
dbo.tblReasons.ReasonID
WHERE (dbo.TestSummary.Date BETWEEN @StartDate AND @EndDate)

Do I need to store the parameters in a varible somehow? How would you do
that?

Thanks

Ernie said:
You stated that you are having the user enter a start and
end date so, to make this work, you will need to
replace "[startdate]" and "[enddate]" with whatever
variable you are using to store what the user enters for
start and end dates. And, if you are using a form to get
these dates, you would also need to include Forms!
-----Original Message-----
When I paste ="Data Between " & [StartDate] & " and " & [EndDate] into an
unbound text box, I receive the following error. Column (startdate) does not
exist in the appropriate rowset.

I assume that this is because the recordset doesn't actually contain a field
called StartDate. Maybe I am going about this all wrong? I tried placing
the Query directly into the record source of the report but I still get the
error.

Any ideas?
Thanks

Steve

It tells you the COLUMNS don't exist? What columns?

This should be an unbound text field.


I am not sure how this is supposed to put the
Click to expand...
parameters that I input in
the
Stored proceedure into fields on the report. When I try this it tells me
that the column does not exist.


yes. Build an uncound text field similar to...

="Data Between " & [StartDate] & " and " & [EndDate]

Rick B



Access 2003 .ADP project on SQL 7.0

I have a stored proceedure that I am using for a report. In that
proceedure
it prompts the user for the StartDate and the EndDate. Is it possible to
have the StartDate and EndDate print in the report header so the user
knows
what the date range of the report is?

Here is the Stored Proceedure in case it is relivant.

SELECT dbo.TestSummary.ProjectNumber, dbo.TestSummary.Date
FROM dbo.TestSummary LEFT OUTER JOIN
dbo.tblReasons ON dbo.TestSummary.Reason =
dbo.tblReasons.ReasonID
WHERE (dbo.TestSummary.Date BETWEEN @StartDate AND @EndDate)

Thanks in advance

Steve
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top