Solved

How do you sort an access date

Posted on 2013-02-06
8
315 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 (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
Industry Leaders: 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!

 
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 48

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views 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 Access…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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