?
Solved

How do you sort an access date

Posted on 2013-02-06
8
Medium Priority
?
316 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:J.R. Sitman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 48

Accepted Solution

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

Author Comment

by:J.R. Sitman
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 93

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 93

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
 

Author Comment

by:J.R. Sitman
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 48

Expert Comment

by:Dale Fye
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:J.R. Sitman
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:J.R. Sitman
ID: 38862342
Thanks
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

762 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