sort order not working using query

[UHNSTrust]

I have had a search through th eforum but cannot find anything that would point to why this is not working.

My query is the following

SELECT tblMgtPlan.*
FROM tblMgtPlan
WHERE (((tblMgtPlan.MgtPlanDiagnosisID)=forms!frmDiagnosis!DiagnosisID))
ORDER BY nz([MgtPlanMeetingDate],#12/31/2049#), nz([MgtPlanMgtPlanDate],#12/31/2049#), tblMgtPlan.MgtPlanDateCreated;

If I take out each ORDER BY clause the sort is not changed. It does not seem to be sorted by any particular field!

Does anybody have any idea why?

Jonathan

 

[SarahG]

I think nz will return either a number or a string. You could try :

ORDER BY cdate(nz([MgtPlanMeetingDate],#12/31/2049#)), cdate(nz([MgtPlanMgtPlanDate],#12/31/2049#)), tblMgtPlan.MgtPlanDateCreated;

to ensure it is ordering by date value rather than the alphabetical order of the dates.

HTH.

 

[UHNSTrust]

SarahG

Thanks for the reply. It worked.

Thanks

Jonathan