×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access 2010 Sort By Calculated date

Access 2010 Sort By Calculated date

Access 2010 Sort By Calculated date

(OP)
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:

Quote:

......,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.

Quote:

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€$

RE: Access 2010 Sort By Calculated date

......,Format((...,"dd/mm/yyyy")
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.

RE: Access 2010 Sort By Calculated date

(OP)
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.

Quote:

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:

Quote:

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€$

RE: Access 2010 Sort By Calculated date

I may be way off, but this logic:

Quote:

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".

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

---- Andy

There is a great need for a sarcasm font.

RE: Access 2010 Sort By Calculated date

(OP)
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€$

RE: Access 2010 Sort By Calculated date

You may want to have 2 fields:

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

Just a suggestion.... smile


---- Andy

There is a great need for a sarcasm font.

RE: Access 2010 Sort By Calculated date

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Access 2010 Sort By Calculated date

Quote (Imaginary Wise Old Programmer)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close