sorting a query

[Nick T]

Hi,
What i thought would be somthing simple, is turning out to be very
frustrating! Any help would be great!

I have a query with 4 columns - Product, Size, Date & Total.
The query is working well, however i want to sort the query, so that the
records appear in date order. I only seem to have the option to sort
'assending' or 'decending', which doesnt work. The date is displayed as eg.
'05 Januarary 08' etc, however when i sort it either assending or decending,
it sorts by the numbers as apposed to the actual date:

eg:
05 Janurary 08
05 February 08
06 Janurary 08
06 February 08
........and so on, which is no good as i have many records for each day of
each month.
I simply want it to display in date order, eg. 05 Jan, 06 Jan 07 Jan etc,
then move onto February.

Any help would be greatly appreciated.
Many thanks
Nick

 

[Pete D.]

Caused by storing the date as text instead of a date. Check the format of
your date field.

 

[Nick T]

The query gets its data from a table. The format of the 'date' column in the
table is 'date/time' and the info appears as eg 18/02/2008 15:40:19. However
this seems to change to eg. 18 Feburary 2008 when the query displays the info
in the datasheet view. I cant find anywhere to change the format of the
query in the design view.
Any suggestions.

Many thanks

 

[Wayne-I-M]

Create a new column in the query and use something like this

NewDate: Format([TableName]![DateField],"Short Date")
or
NewDate: Format([TableName]![DateField],"dd/mm/yy")
or
NewDate: Format([TableName]![DateField],"dddd dd mmm yyyy")

etc
etc

 

[Wayne-I-M]

Or - to sort -right click and open the properties box and set the format from
there.

If your table field is formated as date/time you should be able to sort it.
If not then you may have another problem.

Good luck

 

[Douglas J. Steele]

I don't think so, Wayne.

Format converts the result to a string. The only possible string that will
sort correctly would be

NewDate: Format([TableName]![DateField],"yyyy\-mm\-dd")

Of course, if DateField is in fact a Date/Time field (as opposed to text),
there's no reason to format it in the query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Create a new column in the query and use something like this

NewDate: Format([TableName]![DateField],"Short Date")
or
NewDate: Format([TableName]![DateField],"dd/mm/yy")
or
NewDate: Format([TableName]![DateField],"dddd dd mmm yyyy")

etc
etc


--
Wayne
Manchester, England.

The query gets its data from a table. The format of the 'date' column in
the
table is 'date/time' and the info appears as eg 18/02/2008 15:40:19.
However
this seems to change to eg. 18 Feburary 2008 when the query displays the
info
in the datasheet view. I cant find anywhere to change the format of the
query in the design view.
Any suggestions.

Many thanks

 

[Ken Sheridan]

Nick:

There is a difference between the format of the column and its data type.
The former term seems to have been used in some posts in this thread when the
latter was in fact being referred to.

From what you say it sounds to me like your column is of Date/Time data type
but the query is returning the value as a string formatted dd mmmm yy. What
you need to do is sort on the column separately from its presentation as a
formatted string. In design view you'd do this as follows:

1. Delete any entry in the 'sort' row of the existing date column.
2. Add the date field to a blank column.
3. In the 'sort' row of the additional column select 'ascending'.
4. Uncheck the 'show' check box in the additional column.

The query should now sort the rows returned in date order as its now sorting
on the actual date/time value, not the formatted string equivalent which you
see in the column returned. A date/time value is actually a 64 bit floating
point number under the skin, with its origin at 30 December 1899 00:00:00,
the integer part of the number representing days from the origin, the
fractional part the time of day, the latter being zero, i.e. midnight at the
start of the day, if a date value only is entered. By default the values
are shown in the short date format set as the regional format in Windows, but
the sorting is on the underlying number.

BTW I'd recommend you don't use Date as a field name. It’s the name of a
built in function which returns the current date, so confusion could arise in
some circumstances and the wrong results be returned. A more specific term
such as TransactionDate, OrderDate etc. is better.

Ken Sheridan
Stafford, England

 

[Wayne-I-M]

See below !

Or - to sort -right click and open the properties box and set the format from

there.


--
Wayne
Manchester, England.

I don't think so, Wayne.

Format converts the result to a string. The only possible string that will
sort correctly would be

NewDate: Format([TableName]![DateField],"yyyy\-mm\-dd")

Of course, if DateField is in fact a Date/Time field (as opposed to text),
there's no reason to format it in the query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Create a new column in the query and use something like this

NewDate: Format([TableName]![DateField],"Short Date")
or
NewDate: Format([TableName]![DateField],"dd/mm/yy")
or
NewDate: Format([TableName]![DateField],"dddd dd mmm yyyy")

etc
etc


--
Wayne
Manchester, England.

The query gets its data from a table. The format of the 'date' column in
the
table is 'date/time' and the info appears as eg 18/02/2008 15:40:19.
However
this seems to change to eg. 18 Feburary 2008 when the query displays the
info
in the datasheet view. I cant find anywhere to change the format of the
query in the design view.
Any suggestions.

Many thanks


:

Caused by storing the date as text instead of a date. Check the format
of
your date field.
Hi,
What i thought would be somthing simple, is turning out to be very
frustrating! Any help would be great!

I have a query with 4 columns - Product, Size, Date & Total.
The query is working well, however i want to sort the query, so that
the
records appear in date order. I only seem to have the option to sort
'assending' or 'decending', which doesnt work. The date is displayed
as
eg.
'05 Januarary 08' etc, however when i sort it either assending or
decending,
it sorts by the numbers as apposed to the actual date:

eg:
05 Janurary 08
05 February 08
06 Janurary 08
06 February 08
.......and so on, which is no good as i have many records for each
day of
each month.
I simply want it to display in date order, eg. 05 Jan, 06 Jan 07 Jan
etc,
then move onto February.

Any help would be greatly appreciated.
Many thanks
Nick