Can a report be generated based on a parameter query?

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

 

[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

 

[Douglas J. Steele]

Try using Date() instead of [Date].

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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!

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?

 

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

Try using Date() instead of [Date].

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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!

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?

 

[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
'====================================================

 

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

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.

 

[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