Sorting dates by year first, then month/day (1 Viewer)

JH40 · Feb 3, 2011

Hello all... I'm writing a simple query where I'd like to sort the date result in descending order. The date format is mm/dd/yyyy. When the results return, the sort order is only correct for the given year. For example, the result is:

12/15/2010
10/21/2010
08/01/2010
01/17/2011

I need the 2011 date at the top of this list rather than the bottom. This is most likely very simple, but I'm trying to create a calculated field to do it and have not yet been successful... Any help would be much appreciated. Thanks!

boblarson · Feb 3, 2011

Create a field in your query like this:

SortDate:Format([YourDateFieldNameHere], "yyyymmdd")

And then set the sort to Descending if you want the latest up on top or Ascending if you want the earliest. You can uncheck the Display checkbox for that field so it doesn't display in your query but just sorts by it.

JH40 · Feb 3, 2011

Makes sense--thanks so much. However, is there a way to then display the actual data as mm/dd/yyyy so that it is consistent with the rest of the report formatting? This is a field that will be viewable on the report.

boblarson · Feb 3, 2011

JH40 said:
Makes sense--thanks so much. However, is there a way to then display the actual data as mm/dd/yyyy so that it is consistent with the rest of the report formatting? This is a field that will be viewable on the report.

It looks like your original date field is already formatted that way, so include that field in your query as well.

JH40 · Feb 3, 2011

Ah, the light just went on! Thanks again...

Sorting dates by year first, then month/day (1 Viewer)

JH40

Registered User.

boblarson

Smeghead

JH40

Registered User.

boblarson

Smeghead

JH40

Registered User.

Similar threads

Users who are viewing this thread