Access 2010 Sort By Calculated date

[PWD]

Good morning I have a query within which I have a field that I am using to determine if the appointment date offered has breached our 14 day target, allowing for any adjustments. I think I've got a handle on what to do if those dates fall either over the weekend if today is either Thursday or Friday or on Monday if today is Friday, but my problem, today, is I can't get the calculated date to sort:

......,Format(([N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]),"dd/mm/yyyy")

It will give me 02/09 (2nd September in the UK) before 28/08.

Save A Breach Date
02/09/2019
27/08/2019

I've tried DateVal and CDate but that's just the same.

Is there anything that I'm missing?


Many thanks,
D€$

 

[Andrzejek]

[tt]......,Format((...,"dd/mm/yyyy") [/tt]
Format() function will give you a string in return, so if you sort by it, you get the data in order of Days, then Months, then Years (dd/mm/yyyy)

It would be easier to help you if you would show your query.


---- Andy

There is a great need for a sarcasm font.

 

[PWD]

Thanks Andy, yes it would seem so!!

This is the 'feeder' query, Qry_2wwBreachAvoidance_20170309_Xtrai, that I use to get the last date a patient was referred in to hospital to which cancer site and at what priority.

SELECT Last(dbo_tblMAIN_REFERRALS.N2_4_PRIORITY_TYPE) AS LastOfN2_4_PRIORITY_TYPE
, dbo_tblMAIN_REFERRALS.PATIENT_ID
, Last(dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE) AS [First]
, dbo_tblMAIN_REFERRALS.N2_6_RECEIPT_DATE AS Recd
, Last(dbo_tblMAIN_REFERRALS.L_CANCER_SITE) AS LastOfL_CANCER_SITE
, Last(dbo_tblMAIN_REFERRALS.N2_12_CANCER_TYPE) AS LastOfN2_12_CANCER_TYPE
, dbo_tblDEMOGRAPHICS.N1_2_HOSPITAL_NUMBER
, fPatientStatus([dbo_tblMAIN_REFERRALS]![N2_13_CANCER_STATUS]) AS PatientStatus

FROM dbo_tblMAIN_REFERRALS INNER JOIN dbo_tblDEMOGRAPHICS ON dbo_tblMAIN_REFERRALS.PATIENT_ID = dbo_tblDEMOGRAPHICS.PATIENT_ID
GROUP BY dbo_tblMAIN_REFERRALS.PATIENT_ID, dbo_tblMAIN_REFERRALS.N2_6_RECEIPT_DATE, dbo_tblDEMOGRAPHICS.N1_2_HOSPITAL_NUMBER, fPatientStatus([dbo_tblMAIN_REFERRALS]![N2_13_CANCER_STATUS])
HAVING (((Last(dbo_tblMAIN_REFERRALS.N2_4_PRIORITY_TYPE))="03") AND ((Last(dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE)) Is Null Or (Last(dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE))>Now()) AND ((dbo_tblMAIN_REFERRALS.N2_6_RECEIPT_DATE)>#1/1/2019#) AND ((fPatientStatus([dbo_tblMAIN_REFERRALS]![N2_13_CANCER_STATUS]))<>"No new cancer diagnosis identified"));

Which then runs in here:

SELECT DISTINCT IIf(IsNull([dbo_tblMAIN_REFERRALS].[N2_9_FIRST_SEEN_DATE]),Int(Now()-([N2_6_RECEIPT_DATE]-[N2_14_ADJ_TIME])),[dbo_tblMAIN_REFERRALS].[N2_9_FIRST_SEEN_DATE]-([N2_6_RECEIPT_DATE]+[N2_14_ADJ_TIME])) AS [Estimated 1st Appt Wait]
, dbo_tblDEMOGRAPHICS.N1_2_HOSPITAL_NUMBER AS MRN
, dbo_tblDEMOGRAPHICS.N1_5_SURNAME AS Surname
, dbo_tblDEMOGRAPHICS.N1_6_FORENAME AS [Forename(s)]
, fType([Qry_2wwBreachAvoidance_20170309_Xtrai]![LastOfN2_12_CANCER_TYPE]) AS CancerType
, dbo_tblMAIN_REFERRALS.N2_3_REFERRER_CODE AS [GP Code]
, dbo_tblMAIN_REFERRALS.N2_6_RECEIPT_DATE AS [Ref Recd]
, dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE AS [1st Appointment]
, IIf([N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]<Now()+1,"Breached",IIf(Weekday(Now(),1)=6 And [N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]<Now()+3,"Breached",IIf(Weekday(Now(),1)=5 And [N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]<Now()+3,"Breached",CDate([N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME])))) AS [Save A Breach Date]
, dbo_tblMAIN_REFERRALS.N2_11_FIRST_SEEN_REASON AS [Breach Reason]

FROM (dbo_tblMAIN_REFERRALS INNER JOIN dbo_tblDEMOGRAPHICS ON dbo_tblMAIN_REFERRALS.PATIENT_ID = dbo_tblDEMOGRAPHICS.PATIENT_ID) INNER JOIN Qry_2wwBreachAvoidance_20170309_Xtrai ON dbo_tblDEMOGRAPHICS.PATIENT_ID = Qry_2wwBreachAvoidance_20170309_Xtrai.PATIENT_ID

WHERE (((dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE)>([N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]) And (dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE)>Now()) AND ((dbo_tblMAIN_REFERRALS.N2_4_PRIORITY_TYPE)="03"))

ORDER BY fType([Qry_2wwBreachAvoidance_20170309_Xtrai]![LastOfN2_12_CANCER_TYPE]), IIf([N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]<Now()+1,"Breached",IIf(Weekday(Now(),1)=6 And [N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]<Now()+3,"Breached",IIf(Weekday(Now(),1)=5 And [N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]<Now()+3,"Breached",CDate([N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]))));

And it's this [Save A Breach Date] that I'm trying to sort so that those booking the appointments could check if rescheduling an appointment would prevent a breach.

Does that help any?

Many thanks,
D€$

 

[Andrzejek]

I may be way off, but this logic:

IIf([N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]<Now()+1,"Breached",IIf(Weekday(Now(),1)=6 And [N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]<Now()+3,"Breached",IIf(Weekday(Now(),1)=5 And [N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME]<Now()+3,"Breached",CDate([N2_6_RECEIPT_DATE]+14+[N2_14_ADJ_TIME])))) AS [Save A Breach Date]

returns either "Breached" or something that looks like Date, but is probably just a string, since it has to hold the value of "Breached".

[pre]
Save A Breach Date
Breached
02/09/2019
Breached
27/08/2019
Breached
[/pre]

---- Andy

There is a great need for a sarcasm font.

 

[PWD]

Yeah, that's about right. It appears to sort 'Dates' before it starts on the "Breached".

Save A Breach Date
02/09/2019
27/08/2019
Breached
Breached

Many thanks,
D€$

 

[Andrzejek]

You may want to have 2 fields:

[pre]
Save A Date Breached
02/09/2019 <NULL>
27/08/2019 <NULL>
<NULL> Yes
<NULL> Yes
[/pre]
This way you can sort it any way you want.

Just a suggestion....


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

If your query returns Strings rather than Dates(which are Numeric), then in order to sort as expected, the String Format MUST be YMD rather than DMY or MDY.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[dhookom]

Rule # 167: Never mix data types in the return value of an expression. An expression should only return dates or only return numbers or only return text. The only exception is returning Null in place of any data type.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016