×
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

 Forum Search FAQs Links MVPs

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

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

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

Just traded in my OLD subtlety...
for a NUance!

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

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.