• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

How do you sort an access date

I need to have the date field as day month year day name.
Example May 26 2013 Monday.  How?  See attachment for the way it is now.
date-format.png
0
J.R. Sitman
Asked:
J.R. Sitman
  • 4
  • 2
  • 2
1 Solution
 
Dale FyeCommented:
format([DateFieldName], "mmm dd yyyy dddd")
0
 
J.R. SitmanIT DirectorAuthor Commented:
thanks that worked.  However, I can't figure out why the dates don't sort in order.  It is based on a query that sorts just fine descending, but the Continuous Form doesn't.  See attachment.  

If you can also answer this, I'll increase the points to 500.

Thanks
date-format2.png
0
 
Patrick MatthewsCommented:
Because the Format function returns a string, and when you sort strings, "Apr" comes ahead of "Jan".

Instead of using Format, leave your dates as true date values, and in the control properties for your form textbox, apply the following custom number format:

mmmm dd yyyy dddd

That will display the date as you wish, but with the value still being treated as a date.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Patrick MatthewsCommented:
In the query you use to define the recordset for your form, you should also specify the order you want things displayed in.
0
 
J.R. SitmanIT DirectorAuthor Commented:
The format of the form is correct as fyed suggested.  See latest attachment.  If you look at the previous attachment you see the months go Apr, May, Apr, May, Apr.  That's what I'm trying to figure out why?
date-format3.png
0
 
Dale FyeCommented:
because when you use the Format( ) function, you convert the date into a string.

Add the [DateField] to your query, uncheck the "display" checkbox, and sort on that column instead of this formatted value.

From the fields you showed us, it is obvious that there is another field in the query that the query is sorted by, not this field.
0
 
J.R. SitmanIT DirectorAuthor Commented:
Actually I found the problem.  The Order By had criteria in it.  I'll award points for the original question.

Thanks
0
 
J.R. SitmanIT DirectorAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now