• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 715
  • Last Modified:

Query asking for an Expr1 as a PArameter

Hi Guys
this is the SQl query

SELECT PatientMaster.PatientID, PatientMaster.PatientCode, DiagnosisDetails.GISTDiagnosisDate,

Max(IIf(maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])=#1/1/1000#,"",maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date]))) AS LastContactEvent

,


 DateDiff("d",[DiagnosisDetails].[GISTDiagnosisDate],[LastContactEvent]) AS OPSND


FROM (PatientMaster LEFT JOIN TimeLine ON PatientMaster.PatientID = TimeLine.Patient) INNER JOIN DiagnosisDetails ON PatientMaster.PatientID = DiagnosisDetails.PatientID
GROUP BY PatientMaster.PatientID, PatientMaster.PatientCode, DiagnosisDetails.GISTDiagnosisDate, DateDiff("d",[DiagnosisDetails].[GISTDiagnosisDate],[LastContactEvent]), PatientMaster.Deceased
HAVING (((PatientMaster.Deceased)=No))
ORDER BY PatientMaster.PatientID, Max(IIf(maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])=#1/1/1000#,"",maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])));


When i run this query is asking me for [LastContactEvent] as a Parameter, i hit enter and runs fine, i do not know why
i am using [LastContactEvent] as the max of 3 days i call a function that i create and return the mas of 3 dates.
in OPSND i use [LastContactEvent] to get the difference in days between [lastcontactevent] and another datefield

Any idea


best regards
0
titorober23
Asked:
titorober23
  • 5
  • 3
  • 2
  • +1
4 Solutions
 
cmrobertsonCommented:
have you declared lastcontactevent and set it's initial value
0
 
titorober23Author Commented:
LastContactEvent is not a variable is just the name of a field

Max(IIf(maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])=#1/1/1000#,"",maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date]))) AS LastContactEvent

and i use it in another field
DateDiff("d",[DiagnosisDetails].[GISTDiagnosisDate],[LastContactEvent]) AS OPSND

when i run the query i get the msgbox Enter Parameter Value
[LastContactEvent]
0
 
NicholasSmithCommented:
you have LastContactEvent as a label for a field within your query. when your grouping it doesnt know the data field is part of the recordset. to get around this, use the full field formula and not the name of the field
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
cmrobertsonCommented:
then it needs to be fully qualified with the tablename as well
0
 
StuartMc77Commented:
try changing the name of the LastContactEvent parameter in the datediff funtion to [LastContactEvent2] and see if the message box asks for lastcontactevent or lastcontactevent 2.

If it asks for lastcontactevent2 then the problem is that you need to set a variable called LastContactEvent to the result of the MAX(IIF(...)) statement before you can use it in the datediff function.
0
 
titorober23Author Commented:
how do i set that variable, should i set it global and assign a value in the function, or should i call the maxdate in the datediff function
0
 
NicholasSmithCommented:
your query needs to read:

SELECT PatientMaster.PatientID, PatientMaster.PatientCode, DiagnosisDetails.GISTDiagnosisDate, Max(IIf(maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])=#1/1/1000#,"",maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date]))) AS LastContactEvent, DateDiff("d",[DiagnosisDetails].[GISTDiagnosisDate],Max(IIf(maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])=#1/1/1000#,"",maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date]))) ) AS OPSND FROM (PatientMaster LEFT JOIN TimeLine ON PatientMaster.PatientID = TimeLine.Patient) INNER JOIN DiagnosisDetails ON PatientMaster.PatientID = DiagnosisDetails.PatientID
GROUP BY PatientMaster.PatientID, PatientMaster.PatientCode, DiagnosisDetails.GISTDiagnosisDate, DateDiff("d",[DiagnosisDetails].[GISTDiagnosisDate],[LastContactEvent]), PatientMaster.Deceased
HAVING (((PatientMaster.Deceased)=No))
ORDER BY PatientMaster.PatientID, Max(IIf(maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])=#1/1/1000#,"",maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])));
0
 
StuartMc77Commented:
What Nicholas has said is correct and should work.  The problem here is that the refernce to column LastContactEvent in the datediff function is not able to see use the "on the fly" column that was just made.  What Nicholas has done is simply copy the same formlua into the datediff function.  This works but essentially is doubling up the processing time by doing the same calculation twice.

I believe if you added this before the SELECT part of the statement you could use it through out your query:

SET @LastContactEvent = Max(IIf(maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])=#1/1/1000#,"",maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])))

SELECT ... etc

but if that doesn't work, I'd be happy to find the correct way to do it or I'm sure Nicholas' version will work for you too.
0
 
StuartMc77Commented:
Try this:

DECLARE LastContactEvent Datetime

SET @LastContactEvent = Max(IIf(maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])=#1/1/1000#,"",maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])))

SELECT      PatientMaster.PatientID,
            PatientMaster.PatientCode,
            DiagnosisDetails.GISTDiagnosisDate,
            @LastContactEvent AS LastContactEvent,
            DateDiff("d",[DiagnosisDetails].[GISTDiagnosisDate],@LastContactEvent) AS OPSND

FROM      (PatientMaster LEFT JOIN TimeLine ON PatientMaster.PatientID = TimeLine.Patient)
            INNER JOIN DiagnosisDetails ON PatientMaster.PatientID = DiagnosisDetails.PatientID

GROUP BY      PatientMaster.PatientID,
                  PatientMaster.PatientCode,
                  DiagnosisDetails.GISTDiagnosisDate,
                  DateDiff("d",[DiagnosisDetails].[GISTDiagnosisDate],@LastContactEvent),
                  PatientMaster.Deceased

HAVING (((PatientMaster.Deceased)=No))

ORDER BY      PatientMaster.PatientID,
                  Max(IIf(maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])=#1/1/1000#,"",maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])));
0
 
StuartMc77Commented:
um... but put and @ before LastContactEvent on the DECLARE line lol.
0
 
NicholasSmithCommented:
Instead of having:

 ORDER BY PatientMaster.PatientID,
                  Max(IIf(maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])=#1/1/1000#,"",maxdate([PatientMaster].[LastUpdateDate],[PatientMaster].[LastUpdateDate],[TimeLine].[Start Date])));

wouldnt this work if you wrote:

 ORDER BY PatientMaster.PatientID, @LastContactEvent
0
 
StuartMc77Commented:
HA... lol.. yes I missed that... Thanks Nicholas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now