Can a report be generated based on a parameter query?

S

Sue

I want to base a report on a parameter query. The query asks for a date. I
tried to build a report from it, but it requests a parameter for every single
field.
Suggestions?
 
E

Evi

Post the SQL for your query. I suspect that could be where your problem
lies - either that, or you have put your Parameter's into your report,
rather than the fields which you want to filter.

If you want to be really classy, instead of using a parameter query base
your report on an unfiltered query but put textboxes or combos on your form
and have a button open your report. Combos can be especially elegant,
because you can base them on queries showing the available dates in your
database (use your date field in the queries, but in the Query Properties
in Design View, set Unique Values to Yes so that each date is only shown
once) so users don't have to guess at what dates are available.

The criteria you could use to open your report can then be similar to this

DoCmd.OpenReport "NameOfYourReport",acPreview, , "[YourDateField]>=" &
Format(Me.ComboFromDate,"0") & " AND [YourDateField]<=" &
format(Me.ComboToDate,"0")

Evi
 
S

Sue

Warning - this is a bit long-winded, but here's the SQL. Thanks for your
efforts!~

SELECT tblPatientProtocolActivity.D1Tx, tblPatientProtocolActivity.[ICF
date], tblPatientProtocolActivity.ScreeningDate, tblPatients.LastName,
tblPatients.FirstName, tblPatients.MRN, tblPatientProtocolActivity.Prot_ID,
tblPatientProtocolActivity.PtStatusID, tblPatients.MDID,
tblPatientProtocolActivity.Comments
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.ScreeningDate)>[Date] Or
(tblPatientProtocolActivity.ScreeningDate)=[Date])) OR
(((tblPatientProtocolActivity.[ICF date])>[Date] Or
(tblPatientProtocolActivity.[ICF date])=[Date])) OR
(((tblPatientProtocolActivity.D1Tx)>[Date] Or
(tblPatientProtocolActivity.D1Tx)=[Date]))
ORDER BY tblPatientProtocolActivity.D1Tx DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.ScreeningDate DESC , tblPatients.MDID;

--
Thanks for your time!


Evi said:
Post the SQL for your query. I suspect that could be where your problem
lies - either that, or you have put your Parameter's into your report,
rather than the fields which you want to filter.

If you want to be really classy, instead of using a parameter query base
your report on an unfiltered query but put textboxes or combos on your form
and have a button open your report. Combos can be especially elegant,
because you can base them on queries showing the available dates in your
database (use your date field in the queries, but in the Query Properties
in Design View, set Unique Values to Yes so that each date is only shown
once) so users don't have to guess at what dates are available.

The criteria you could use to open your report can then be similar to this

DoCmd.OpenReport "NameOfYourReport",acPreview, , "[YourDateField]>=" &
Format(Me.ComboFromDate,"0") & " AND [YourDateField]<=" &
format(Me.ComboToDate,"0")

Evi




Sue said:
I want to base a report on a parameter query. The query asks for a date. I
tried to build a report from it, but it requests a parameter for every single
field.
Suggestions?
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Try using Date() instead of [Date].

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sue said:
Warning - this is a bit long-winded, but here's the SQL. Thanks for your
efforts!~

SELECT tblPatientProtocolActivity.D1Tx, tblPatientProtocolActivity.[ICF
date], tblPatientProtocolActivity.ScreeningDate, tblPatients.LastName,
tblPatients.FirstName, tblPatients.MRN,
tblPatientProtocolActivity.Prot_ID,
tblPatientProtocolActivity.PtStatusID, tblPatients.MDID,
tblPatientProtocolActivity.Comments
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID
=
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.ScreeningDate)>[Date] Or
(tblPatientProtocolActivity.ScreeningDate)=[Date])) OR
(((tblPatientProtocolActivity.[ICF date])>[Date] Or
(tblPatientProtocolActivity.[ICF date])=[Date])) OR
(((tblPatientProtocolActivity.D1Tx)>[Date] Or
(tblPatientProtocolActivity.D1Tx)=[Date]))
ORDER BY tblPatientProtocolActivity.D1Tx DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.ScreeningDate DESC , tblPatients.MDID;

--
Thanks for your time!


Evi said:
Post the SQL for your query. I suspect that could be where your problem
lies - either that, or you have put your Parameter's into your report,
rather than the fields which you want to filter.

If you want to be really classy, instead of using a parameter query base
your report on an unfiltered query but put textboxes or combos on your
form
and have a button open your report. Combos can be especially elegant,
because you can base them on queries showing the available dates in your
database (use your date field in the queries, but in the Query
Properties
in Design View, set Unique Values to Yes so that each date is only shown
once) so users don't have to guess at what dates are available.

The criteria you could use to open your report can then be similar to
this

DoCmd.OpenReport "NameOfYourReport",acPreview, , "[YourDateField]>=" &
Format(Me.ComboFromDate,"0") & " AND [YourDateField]<=" &
format(Me.ComboToDate,"0")

Evi




Sue said:
I want to base a report on a parameter query. The query asks for a
date. I
tried to build a report from it, but it requests a parameter for every single
field.
Suggestions?
Click to expand...
Click to expand...
Click to expand...
 
S

Sue

I tried the following & got a syntax error:

SELECT tblPatientProtocolActivity.D1Tx, tblPatientProtocolActivity.[ICF
date], tblPatientProtocolActivity.ScreeningDate, tblPatients.LastName,
tblPatients.FirstName, tblPatients.MRN, tblPatientProtocolActivity.Prot_ID,
tblPatientProtocolActivity.PtStatusID, tblPatients.MDID,
tblPatientProtocolActivity.Comments
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.ScreeningDate)>Date() Or
(tblPatientProtocolActivity.ScreeningDate Date())) OR
(((tblPatientProtocolActivity.[ICF date])> Date() Or
(tblPatientProtocolActivity.[ICF date])= Date())) OR
(((tblPatientProtocolActivity.D1Tx)> Date() Or
(tblPatientProtocolActivity.D1Tx)= Date()))
ORDER BY tblPatientProtocolActivity.D1Tx DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.ScreeningDate DESC , tblPatients.MDID;

I'm not sure where to use Date() - and I don't know why that would be the
best option. Can you please help me to understand?

Thanks.
--
Thanks for your time!


Douglas J. Steele said:
Try using Date() instead of [Date].

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sue said:
Warning - this is a bit long-winded, but here's the SQL. Thanks for your
efforts!~

SELECT tblPatientProtocolActivity.D1Tx, tblPatientProtocolActivity.[ICF
date], tblPatientProtocolActivity.ScreeningDate, tblPatients.LastName,
tblPatients.FirstName, tblPatients.MRN,
tblPatientProtocolActivity.Prot_ID,
tblPatientProtocolActivity.PtStatusID, tblPatients.MDID,
tblPatientProtocolActivity.Comments
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID
=
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.ScreeningDate)>[Date] Or
(tblPatientProtocolActivity.ScreeningDate)=[Date])) OR
(((tblPatientProtocolActivity.[ICF date])>[Date] Or
(tblPatientProtocolActivity.[ICF date])=[Date])) OR
(((tblPatientProtocolActivity.D1Tx)>[Date] Or
(tblPatientProtocolActivity.D1Tx)=[Date]))
ORDER BY tblPatientProtocolActivity.D1Tx DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.ScreeningDate DESC , tblPatients.MDID;

--
Thanks for your time!


Evi said:
Post the SQL for your query. I suspect that could be where your problem
lies - either that, or you have put your Parameter's into your report,
rather than the fields which you want to filter.

If you want to be really classy, instead of using a parameter query base
your report on an unfiltered query but put textboxes or combos on your
form
and have a button open your report. Combos can be especially elegant,
because you can base them on queries showing the available dates in your
database (use your date field in the queries, but in the Query
Properties
in Design View, set Unique Values to Yes so that each date is only shown
once) so users don't have to guess at what dates are available.

The criteria you could use to open your report can then be similar to
this

DoCmd.OpenReport "NameOfYourReport",acPreview, , "[YourDateField]>=" &
Format(Me.ComboFromDate,"0") & " AND [YourDateField]<=" &
format(Me.ComboToDate,"0")

Evi




I want to base a report on a parameter query. The query asks for a
date. I
tried to build a report from it, but it requests a parameter for every
single
field.
Suggestions?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

As posted you are missing a comparison operator in the second condition.

Why not the following.

SELECT tblPatientProtocolActivity.D1Tx, tblPatientProtocolActivity.[ICF
date], tblPatientProtocolActivity.ScreeningDate, tblPatients.LastName,
tblPatients.FirstName, tblPatients.MRN, tblPatientProtocolActivity.Prot_ID,
tblPatientProtocolActivity.PtStatusID, tblPatients.MDID,
tblPatientProtocolActivity.Comments
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON
tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE tblPatientProtocolActivity.ScreeningDate>=Date() Or
tblPatientProtocolActivity.[ICF date])>=Date() Or
tblPatientProtocolActivity.D1Tx>+ Date()
ORDER BY tblPatientProtocolActivity.D1Tx DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.ScreeningDate DESC , tblPatients.MDID;

Date() is a function that returns the current system date.
[Date] would be a field with the name "Date" and it would exists in some
table. In this SQL it would have to exist in table tblPatients or in
tblPatientProctocolActivity.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
S

Sue

I pasted your SQL but am told I have an extra ) somewhere now.
Sorry to be a bother, but all these ('s and )'s create a bit of a forest for
me, and I can't find the problem.
I truly appreciate your time.
--
Thanks for your time!


John Spencer said:
As posted you are missing a comparison operator in the second condition.

Why not the following.

SELECT tblPatientProtocolActivity.D1Tx, tblPatientProtocolActivity.[ICF
date], tblPatientProtocolActivity.ScreeningDate, tblPatients.LastName,
tblPatients.FirstName, tblPatients.MRN, tblPatientProtocolActivity.Prot_ID,
tblPatientProtocolActivity.PtStatusID, tblPatients.MDID,
tblPatientProtocolActivity.Comments
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON
tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE tblPatientProtocolActivity.ScreeningDate>=Date() Or
tblPatientProtocolActivity.[ICF date])>=Date() Or
tblPatientProtocolActivity.D1Tx>+ Date()
ORDER BY tblPatientProtocolActivity.D1Tx DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.ScreeningDate DESC , tblPatients.MDID;

Date() is a function that returns the current system date.
[Date] would be a field with the name "Date" and it would exists in some
table. In this SQL it would have to exist in table tblPatients or in
tblPatientProctocolActivity.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I tried the following & got a syntax error:

SELECT tblPatientProtocolActivity.D1Tx, tblPatientProtocolActivity.[ICF
date], tblPatientProtocolActivity.ScreeningDate, tblPatients.LastName,
tblPatients.FirstName, tblPatients.MRN, tblPatientProtocolActivity.Prot_ID,
tblPatientProtocolActivity.PtStatusID, tblPatients.MDID,
tblPatientProtocolActivity.Comments
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.ScreeningDate)>Date() Or
(tblPatientProtocolActivity.ScreeningDate Date())) OR
(((tblPatientProtocolActivity.[ICF date])> Date() Or
(tblPatientProtocolActivity.[ICF date])= Date())) OR
(((tblPatientProtocolActivity.D1Tx)> Date() Or
(tblPatientProtocolActivity.D1Tx)= Date()))
ORDER BY tblPatientProtocolActivity.D1Tx DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.ScreeningDate DESC , tblPatients.MDID;

I'm not sure where to use Date() - and I don't know why that would be the
best option. Can you please help me to understand?

Thanks.
Click to expand...
Click to expand...
 
J

John Spencer

Try the following. What I posted earlier had at least two syntax errors.
The extra parens was in the second line in the where clause.
There as also a >+ instead of >= in the third line of the where clause.

SELECT tblPatientProtocolActivity.D1Tx
, tblPatientProtocolActivity.[ICF date]
, tblPatientProtocolActivity.ScreeningDate
, tblPatients.LastName
, tblPatients.FirstName
, tblPatients.MRN
, tblPatientProtocolActivity.Prot_ID
, tblPatientProtocolActivity.PtStatusID
, tblPatients.MDID
, tblPatientProtocolActivity.Comments
FROM tblPatients INNER JOIN tblPatientProtocolActivity
ON tblPatients.PtID =tblPatientProtocolActivity.PtID
WHERE tblPatientProtocolActivity.ScreeningDate >= Date() Or
tblPatientProtocolActivity.[ICF date] >= Date() Or
tblPatientProtocolActivity.D1Tx >= Date()
ORDER BY tblPatientProtocolActivity.D1Tx DESC
, tblPatientProtocolActivity.[ICF date] DESC
, tblPatientProtocolActivity.ScreeningDate DESC
, tblPatients.MDID;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameter Query In Report Title 3
How do I reference a parameter in a report? 2
Parameter Query of "between ...and " 1
print a date range from parameter query on a report header? 2
Subreport - Multiple parameter entries 2
IIf in a report based on select query -- problem w/parameters 2
Access Report on Parameter Query 3
Parameter Value as a Report Heading (Label/Text) 1

Top