Solved

How do you sort an access date

Posted on 2013-02-06
8
309 Views
Last Modified: 2013-02-06
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
Comment
Question by:jrsitman
  • 4
  • 2
  • 2
8 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 300 total points
ID: 38862284
format([DateFieldName], "mmm dd yyyy dddd")
0
 

Author Comment

by:jrsitman
ID: 38862294
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38862303
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38862304
In the query you use to define the recordset for your form, you should also specify the order you want things displayed in.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:jrsitman
ID: 38862315
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38862330
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
 

Author Comment

by:jrsitman
ID: 38862339
Actually I found the problem.  The Order By had criteria in it.  I'll award points for the original question.

Thanks
0
 

Author Closing Comment

by:jrsitman
ID: 38862342
Thanks
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now