sort date??

[associates]

Hi,

I was wondering if anyone would be able to tell how to sort date of type text in Access database?

The date that is of type text is stored in the table in the following format

1-August-2005 instead of 1/8/2005.

But when i press a button "sort By date in desc", it gives me
7-November-2005
3-October-2005
2-November-2005
29-August-2005

My code for the button "sort By date in desc" is as follows
strSQL = "SELECT DISTINCTROW Salary.Sal_ID, Staff_register.First_Name, Staff_register.Last_Name, Salary.Staff_ID, Salary.Date "
strSQL = strSQL & "FROM Salary INNER JOIN Staff_register ON Salary.Staff_ID=Staff_register.Staff_ID "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me!List0.RowSource = strSQL
Me!List0.Requery

YOur help is very appreciated. Thank you in advance

 

[lupins46]

You cannot sort text dates into date order.
You need to create another field in your query.
Cdate(datefieldname) as MyDate
and sort on that.

 

[associates]

Hi Lupins46,

Thank you for your help.

Sorry for being slow to catch what you're suggesting.

> You need to create another field in your query.
How do i create another field in the query builder? This is what i have tried. i went to an empty field in the query builder then put in "=cdate([Date])" to the expression builder. It successfully converted but the sort did not work. Do i need to create a new field in my table?

Thank you so much for your help in advance

 

[sdk]

Hi,

The problem is that the dates are being stored as text strings. The results that you have shown are therefore correct.

You need to tell access to format them to a date, by:
Format([yourFieldName],"Short Date")

You can use this in your 'Order by' clause as
"Order By Format([" & col & "], 'Short Date')"

Cheers

Steve

 

[associates]

Hi Steve,

I tried to put in the code as you suggested as follows

strSQL = strSQL & "ORDER BY Format([" & col & "], 'Short Date')" & " " & xorder

And i'm still getting the same answer that is

7-November-2005
3-October-2005
29-August-2005
26-August-2005
25-October-2005

Here is my other code that invokes the sorting function
Private Sub CM_DateAsc_Click()
'Set Date field order in ascending order
Dim response As Integer
response = basOrderby("Date", "asc")
And for CM_DateDesc_Click() is
Dim response As Integer
response = basOrderby("Date", "DESC")

Thank you in advance

 

[sdk]

Hi,

oops ... my stupidity ... formatting string returns strings.

cdate(date as string)
will return a date. But it will not handle zero length strings and the date string must be correctly formatted.

Also the button events appear to change the order by clause of the SQL statement. That issue must also be managed, and it may be that the module must be modified to address the structure of the statement.

Try that and let me know how you get on.

fyi: you can use scalar types to reference the ordinal position of a col to order by
eg Select field1, field2 ... from tble order by field2
is the same as
Select field1, field2 ... from tble order by 2
this might help with the module reordering.

Cheers

Steve

 

[lupins46]

To create another field in a query you go to the first empty column and in the 'field' cell for that column you do ...
MyDate:Cdate([Date])

You can then sort on this column.
You should be aware that 'Date' is a very undesirable name for a field as it is a reserved word.
Every time you use it as a field name you should enclose it in [ ]to ensure it is not confused with the reserved word Date (with no brackets).

 

[PHV]

strSQL = strSQL & "ORDER BY CDate([" & col & "]) " & xorder

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886