If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dates do not sort correctly
Hi All,
I'm Running Microsoft Access 2007. In reports, when I sort my report by date - access does not sort it correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it is reading 12/10/09 as 12/01/09). Any clues? Thanks, Buddy |
#2
|
|||
|
|||
Dates do not sort correctly
Then you are storing a string that looks like a date or you have applied the
format function to a datetime field and that converted the date into a string. You can use the DateValue function to convert the string into a datetime type and then sort on that. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Thanks wrote: Hi All, I'm Running Microsoft Access 2007. In reports, when I sort my report by date - access does not sort it correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it is reading 12/10/09 as 12/01/09). Any clues? Thanks, Buddy |
#3
|
|||
|
|||
Dates do not sort correctly
Apparently your date value is actually a string/text. Are you sorting by an
actual date field or are you sorting on a formatted date? If you have formatted the date field in your report's record source, don't. Leave the formatting to the control in the report where you display the date. -- Duane Hookom Microsoft Access MVP "Thanks, Buddy" wrote: Hi All, I'm Running Microsoft Access 2007. In reports, when I sort my report by date - access does not sort it correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it is reading 12/10/09 as 12/01/09). Any clues? Thanks, Buddy |
#4
|
|||
|
|||
Dates do not sort correctly
Thanks Duane, The field in my source table is a date field that the user
enters. I can only format it as a date. When I sort the date in the report, Access recognizes it as a date field, but does not sort it as a date field. Thanks, Buddy "Duane Hookom" wrote: Apparently your date value is actually a string/text. Are you sorting by an actual date field or are you sorting on a formatted date? If you have formatted the date field in your report's record source, don't. Leave the formatting to the control in the report where you display the date. -- Duane Hookom Microsoft Access MVP "Thanks, Buddy" wrote: Hi All, I'm Running Microsoft Access 2007. In reports, when I sort my report by date - access does not sort it correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it is reading 12/10/09 as 12/01/09). Any clues? Thanks, Buddy |
#5
|
|||
|
|||
Dates do not sort correctly
Formating creates a string - text. Use the DateTime field unformated for
sorting and display your formated one in report. -- Build a little, test a little. "Thanks, Buddy" wrote: Thanks Duane, The field in my source table is a date field that the user enters. I can only format it as a date. When I sort the date in the report, Access recognizes it as a date field, but does not sort it as a date field. Thanks, Buddy "Duane Hookom" wrote: Apparently your date value is actually a string/text. Are you sorting by an actual date field or are you sorting on a formatted date? If you have formatted the date field in your report's record source, don't. Leave the formatting to the control in the report where you display the date. -- Duane Hookom Microsoft Access MVP "Thanks, Buddy" wrote: Hi All, I'm Running Microsoft Access 2007. In reports, when I sort my report by date - access does not sort it correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it is reading 12/10/09 as 12/01/09). Any clues? Thanks, Buddy |
#6
|
|||
|
|||
Dates do not sort correctly
Hey everyone, thanks for your help...but maybe I should post this under
newbie....I don't really know how to do what you are suggesting. I looked up format type...and it only offers me different type of date formats. "John Spencer" wrote: Then you are storing a string that looks like a date or you have applied the format function to a datetime field and that converted the date into a string. You can use the DateValue function to convert the string into a datetime type and then sort on that. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Thanks wrote: Hi All, I'm Running Microsoft Access 2007. In reports, when I sort my report by date - access does not sort it correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it is reading 12/10/09 as 12/01/09). Any clues? Thanks, Buddy . |
#7
|
|||
|
|||
Dates do not sort correctly
Three "mature" sages have all suggested you either have a text/string date
value or have somehow converted it to a test/string value. Can you share the SQL view of your report's record source? I assume you realize the sorting in the record source has little or no affect on the sorting in the report. -- Duane Hookom Microsoft Access MVP "KARL DEWEY" wrote: Formating creates a string - text. Use the DateTime field unformated for sorting and display your formated one in report. -- Build a little, test a little. "Thanks, Buddy" wrote: Thanks Duane, The field in my source table is a date field that the user enters. I can only format it as a date. When I sort the date in the report, Access recognizes it as a date field, but does not sort it as a date field. Thanks, Buddy "Duane Hookom" wrote: Apparently your date value is actually a string/text. Are you sorting by an actual date field or are you sorting on a formatted date? If you have formatted the date field in your report's record source, don't. Leave the formatting to the control in the report where you display the date. -- Duane Hookom Microsoft Access MVP "Thanks, Buddy" wrote: Hi All, I'm Running Microsoft Access 2007. In reports, when I sort my report by date - access does not sort it correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it is reading 12/10/09 as 12/01/09). Any clues? Thanks, Buddy |
#8
|
|||
|
|||
Dates do not sort correctly
=?Utf-8?B?S0FSTCBERVdFWQ==?=
wrote in : Formating creates a string - text. Use the DateTime field unformated for sorting and display your formated one in report. In fact, don't ever format for display values in the SQL Recordsource -- that's a display issue and belongs in the presentation layer, and not in the data retrieval layer. The only exception to that would be in a form where you might want to filter/sort on the formatted value. This would not apply to dates, but it could apply to other values that you might be using an expression for (e.g., and Nz() wrapper). -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Thread Tools | |
Display Modes | |
|
|