You cannot refer to the alias of a field in the where clause. You need to
refer to the field itself
Since you are using queries as sources, you could alias the name in the
query - Then you could use the alias.
So in your qryGetsC1SDate set the alias for the Min(Visit_Date) to C1S.
Then in this query you can use C1S in the where clause
I think that you need to modify your query as follows to get it to work..
Note the modification is in the where clause
SELECT qryGets1stTxForDisease.patient_chart_number,
tblXportDisease.date_referral_med_oncologist AS [MO Referral],
qryGetsC1SDate.MinOfvisit_date AS C1S,
qryGets1stTxForDisease.registration_date,
qryGets1stTxForDisease.MinOfvisit_date AS [MO Tx]
, Nz((DateDiff("d",[MO Referral],[C1S])),0) AS [Referral To Consult],
qryGets1stTxForDisease.FirstOfdecision_to_treat_date AS DTT,
qryGets1stTxForDisease.FirstOfready_to_treat_date AS RTT,
(DateDiff("d",[C1S],[MO Tx])) AS [Consult To Treat]
, DateDiff("d",[RTT],[MO Tx]) AS [RTT To Treat]
, Nz((DateDiff("d",[MO Referral],[C1S])),0)+Nz((DateDiff("d",[C1S],[MO
Tx])),0) AS TotalWait
FROM (qryGets1stTxForDisease INNER JOIN tblXportDisease ON
(qryGets1stTxForDisease.registration_date =
tblXportDisease.registration_date) AND
(qryGets1stTxForDisease.patient_chart_number =
tblXportDisease.patient_chart_number)) LEFT JOIN qryGetsC1SDate ON
(qryGets1stTxForDisease.patient_chart_number =
qryGetsC1SDate.patient_chart_number) AND
(qryGets1stTxForDisease.registration_date =
qryGetsC1SDate.registration_date)
WHERE qryGets1stTxForDisease.MinOfvisit_date Between #8/1/2007# And
#8/31/2007#
AND
Nz(DateDiff("d",tblXportDisease.date_referral_med_oncologist,qryGetsC1SDate.MinOfvisit_date
),0)+Nz(DateDiff("d",qryGetsC1SDate.MinOfvisit_date
,qryGets1stTxForDisease.MinOfvisit_date),0)<100
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Chris said:
Copied from SQL view: S N I P
Thanks again.
Christine
John Spencer said:
Can you post the SQL of your query? (View: SQL from the menu).
I see no reason for you to get the prompt from what you have posted.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Chris said:
I did as you suggested, I still get the correct value, but as soon as I
add
the criteria of <100 I am prompted for each of the fields in my
statement.
One strange thing I noticed is that if I use "filter by form" filter on
my
query datasheet view and add <100 it works!
Any other ideas that might help.
Thanks.
Christine
:
Unfortunately, you need to repeat the entire calculation for each item
TotalWait: Nz((DateDiff("d",[MO Referral],[C1S])),0) +
Nz((DateDiff("d",[C1S],[MO Tx])),0)
If you weren not applying criteria to TotalWait you M I G H T have
been
able
to use your expression.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
