Averages - data type mismatch (1 Viewer)

[tezread]

I have a query

SELECT Avg(qryWaitingTimes.ReferralToClinicdate) AS AvgOfReferralToClinicdate, Avg(qryWaitingTimes.ReferralToTreatment) AS AvgOfReferralToTreatment
FROM qryWaitingTimes;


But when I run it it comes up with data type mismatch error?

 

[Guus2005]

Are you sure that every value in qryWaitingTimes.ReferralToClinicdate and qryWaitingTimes.ReferralToTreatment is a number?

You can use the Nz function to avoid NULLs

SELECT Avg(nz(qryWaitingTimes.ReferralToClinicdate,0)) AS AvgOfReferralToClinicdate, Avg(nz(qryWaitingTimes.ReferralToTreatment,0)) AS AvgOfReferralToTreatment
FROM qryWaitingTimes;

HTH

 

[tezread]

there are some null values in there and the nz function could help with that. I tried suggested syntax but the same message comes up

 

[tezread]

The origins of the fields are created as thus:

ReferralToClinicdate: DateDiff("d",[Date_referred],[Date_seen])

They are custom fields

 

[Guus2005]

not sure what you mean with "custom fields"?

Check for NULL values before calculating the difference:

ReferralToClinicdate: iif([Date_referred] is null or [Date_seen] is null,0,DateDiff("d",[Date_referred],[Date_seen]))

Enjoy!

 

[tezread]

Custom fields is probably the wrong term. In my query I have

SELECT tblPatient.Patient_Name, tblEpisode.EpisodeID, tblPatient.Unit_Number, tblPatient.PatientID, tblPatient.DOB, tblPatient.Gender, tblPatient.Postcode, tblPatient.Ethnic_Origin, tblPatient.GP_DR, tblEpisode.Date_Referred, tblEpisode.Date_seen, tblEpisode.ECG_RATE, tblEpisode.Sinus_Rhythm, tblEpisode.AF, tblEpisode.[A/Flutter], tblEpisode.QTc, tblEpisode.And_Others, tblEpisode.Syncope, tblEpisode.Pre_syncope, tblEpisode.Palpitations, tblEpisode.Missed_Beats, tblEpisode.Exercise, tblEpisode.Stress_EmotionalUpset, tblEpisode.Precipitating_factors_Chest_Pain, tblEpisode.Medication, tblEpisode.Precipitating_factors_Dyspnoea, tblEpisode.Alcohol, tblEpisode.Caffeine, tblEpisode.Precipitating_factors_other, tblEpisode.Associated_symptoms_Dyspnoea, tblEpisode.Associated_symptoms_Chest_Pain, tblEpisode.Collapse, tblEpisode.Dizziness, tblEpisode.Sweating, tblEpisode.Associated_symptoms_Other, tblEpisode.Previous_Arrhythmia, tblEpisode.IHD, tblEpisode.MI, tblEpisode.Angina, tblEpisode.PCI, tblEpisode.CABG, tblEpisode.Valve_Dis, tblEpisode.Rheumatic_fever, tblEpisode.Hyperthyroidism, tblEpisode.Asthma, tblEpisode.DM, tblEpisode.Smoker, tblEpisode.Hypertension, tblEpisode.Past_medical_history_Other, tblEpisode.Chest_sounds, tblEpisode.Heart_sounds, tblEpisode.Initial_drugs_Digoxin, tblEpisode.Initial_drugs_Bblockers, tblEpisode.Initial_drugs_CaBlockers, tblEpisode.Initial_drugs_Asprin, tblEpisode.Initial_drugs_Plavix, tblEpisode.Initial_drugs_Amio, tblEpisode.Initial_drugs_warfarin, tblEpisode.Initial_drugs_Flecainide, tblEpisode.Treatment_options_discussed, tblEpisode.Clinicalmanagement, tblEpisode.Thromboembolic_risk, tblEpisode.Bloods_TSH, tblEpisode.ECG_Monitoring_required, tblEpisode.Primary_Results, IIf([Primary_results] Is Null,"No primary result",[Primary_Results]) AS Primary_resultreport, tblEpisode.Secondary_results, tblEpisode.Date_Done, tblEpisode.Echo_required, tblEpisode.Echo_result, tblEpisode.Date_Done1, tblEpisode.ETT_RESULT, tblEpisode.Dischargemeds_Digoxin, tblEpisode.Dischargemeds_Bblockers, tblEpisode.Dischargemeds_CaBlockers, tblEpisode.Dischargemeds_Asprin, tblEpisode.Dischargemeds_Plavix, tblEpisode.Dischargemeds_Warfarin, tblEpisode.Dischargemeds_Amiodorone, tblEpisode.Dischargemeds_Fleccanide, tblEpisode.Dischargemeds_Date, tblEpisode.Outcome, IIf([Outcome] Is Null,"No Outcome",[Outcome]) AS Outcomereport, tblEpisode.Specialist_Intervention, tblEpisode.Arrhythmia_Identified, IIf([Date_referred] Is Null Or [Date_seen] Is Null,0,DateDiff("d",[Date_referred],[Date_seen])) AS ReferralToClinicdate, IIf([Date_referred] Is Null Or [Dischargemeds_Date] Is Null,0,DateDiff("d",[Date_referred],[Dischargemeds_Date])) AS ReferralToTreatment
FROM tblPatient LEFT JOIN tblEpisode ON tblPatient.PatientID = tblEpisode.PatientID;


The columns highlighted don't belong to a table thats what I meant - they are created on the fly

 

[tezread]

still getting data type mismatch error though.

 

[tezread]

found the problem!!!
Data type for DischargeMeds_Date is Text"!!!!

Doh!!

 

[Guus2005]

your welcome.