Solved

How do you sort an access date

Posted on 2013-02-06
8
314 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
  • 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: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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

749 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