Query not working due to one new record

[ambrn14]

I have a query that has been running perfectly until yesterday. There is a parameter criteria which states "Which Neurologist?". It works for all Neurologist but one. Here is the error message that I get- "This expression is typed incorrectly, or it is too complex to be evaluate. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." I don't understand why the expression would be too complex based on a criteria that I have entered and that has worked before.

I have narrowed the problem down to one record that involves this particular Neurologist. But I don't know why it does not like this record or what is different about this record. When I run the query prior to this record's date with the one Neurologist, the query works. I have to have this query! (If there was a smilie face that was pulling out her hair, I would use it!!)

Here is the SQL:

"SELECT Avg((DateDiff("n",CDate([doordate]+[doortime]),CDate([IVtPAStartdate]+[IVtPAStarttime])))) AS [Overall Door to IV tPA], Avg(DateDiff("n",[OnsetDate]+[OnsetTime],[DoorDate]+[DoorTime])) AS [Overall Onset to Door], Avg((DateDiff("n",[DoorDate]+[DoorTime],[CodeStrokeActivationDate]+[CodeStrokeActivationTime]))) AS [Overall Door to Code Stroke Activation], Avg((DateDiff("n",CDate([DoorDate]+[DoorTime]),CDate([EDMDDateofEval]+[EDMDTimeofEval])))) AS [Overall Door to ED MD Eval], Avg((DateDiff("n",[DoorDate]+[DoorTime],[CTOrderDate]+[CTOrderTime]))) AS [Overall Door to CT Order], Avg((DateDiff("n",[DoorDate]+[DoorTime],[CTCompleteDate]+[CTCompleteTime]))) AS [Overall Door to CT Complete], Avg((DateDiff("n",[CTOrderDate]+[CTOrderTime],[CTCompleteDate]+[CTCompleteTime]))) AS [Overall CT Order to CT Complete], Avg((DateDiff("n",[DoorDate]+[DoorTime],[CTInterpretationDate]+[CTInterpretationTime]))) AS [Overall Door to CT Interpretation], Avg((DateDiff("n",[CTOrderDate]+[CTOrderTime],[CTInterpretationDate]+[CTInterpretationTime]))) AS [Overall CT Order to CT Interpretation], Avg((DateDiff("n",[DoorDate]+[DoorTime],[LabsOrderDate]+[LabsOrderTime]))) AS [Overall Door to Labs Order], Avg((DateDiff("n",[DoorDate]+[DoorTime],[LabsCompleteDate]+[LabsCompleteTime]))) AS [Overall Door to Labs Complete], Avg((DateDiff("n",[LabsOrderDate]+[LabsOrderTime],[LabsCompleteDate]+[LabsCompleteTime]))) AS [Overall Labs Order to Labs Complete], Avg((DateDiff("n",[CodeStrokeActivationDate]+[CodeStrokeActivationTime],[NeuroClinicianResponseDate]+[NeuroClinicianResponseTime]))) AS [Overall CS Activation to Neuro Clinician Response], Avg((DateDiff("n",[DoorDate]+[DoorTime],[NeuroClinicianResponseDate]+[NeuroClinicianResponseTime]))) AS [Overall Door to Neuro Clinician Response], Avg((DateDiff("n",[NeuroClinicianResponseDate]+[NeuroClinicianResponseTime],[IVtPAOrderDate]+[IVtPAOrderTime]))) AS [Overall Neuro Clinician Response to tPA Order], Avg((DateDiff("n",[IVtPAOrderDate]+[IVtPAOrderTime],[IVtPAStartDate]+[IVtPAStartTime]))) AS [Overall tPA Order to tPA Start Time], Avg((DateDiff("n",[DoorDate]+[DoorTime],[IVtPAOrderDate]+[IVtPAOrderTime]))) AS [Overall Door to tPA Order]
FROM tblCSNeurologists INNER JOIN (tblCodeStrokePts INNER JOIN tblPatientCSNeurologists ON tblCodeStrokePts.CodeStrokeID = tblPatientCSNeurologists.CodeStrokeID) ON tblCSNeurologists.CSNeurKey = tblPatientCSNeurologists.CSNeurologist
WHERE (((tblCSNeurologists.CSNeurologist)=[Which Neurologist?]))
GROUP BY tblCodeStrokePts.CodeStrokeDate, tblCodeStrokePts.DoorDate, tblCodeStrokePts.DoorTime, tblCodeStrokePts.IVtPAStartDate, tblCodeStrokePts.IVtPAStartTime
HAVING (((tblCodeStrokePts.CodeStrokeDate) Between [Forms]![frmQuerySwitchboard]![txtStartDate] And [Forms]![frmQuerySwitchboard]![txtEndDate]) AND ((tblCodeStrokePts.DoorDate) Is Not Null) AND ((tblCodeStrokePts.DoorTime) Is Not Null) AND ((tblCodeStrokePts.IVtPAStartDate) Is Not Null) AND ((tblCodeStrokePts.IVtPAStartTime) Is Not Null));"

I greatly appreciate any help that anyone can give. Thank you!

 

[Les Isaacs]

I'm no expert, but I'd say that some of the other data for the problem neurologist is casuing the problem - perhaps there's a division by zero, or a date that's been entered as text (I've had that error message for both these reasons)? Have a close look at all the other fields for the problem record.

 

[ambrn14]

Thanks! I will look at this record again. I just don't see anything different about this record.

 

[PNGBill]

If you have a Select Query that works for all records except one, then my assumption is there must be some issue with this one record's data.

I note your select query includes other queries. Recheck these as they may be filtering that one record prior to your new query running. As Les advises.

We had this issue. I or many thousands and it bioled down to some obscure pce of data that prevented the record being passed by an equally obscure query.

 

[ambrn14]

Thanks for the replies.

I found the problem. There was an error due to a missing data element in one of the queries within the main query and therefore it would not work. So you guys were right that it was in the problem record.

Thank you!