Date sort problem

[spud]

I am using the following to search an database:

Private Sub Command1_Click()
Data1.RecordSource = "SELECT * FROM dates WHERE Date BETWEEN #" & Text1.Text & "# AND #" & Text2.Text & "#;"
Data1.Refresh
End Sub

Private Sub Command2_Click()
Data1.RecordSource = "SELECT * FROM dates order by year(Date), month(Date), day(Date)"
Data1.Refresh
End Sub

the problem is that after the search between two dates is completed the dates are not in order, hense Command2. However this refreshies the current search and lists all the dates in the database

If I try it the other way its the same result


Any help?

[honeybee]

Try using the FORMAT() function for the sort:

ORDER BY FORMAT(DateField, 'yyyy mm dd')

.

[spud]

I dont know this command

where does it go in the grand scheam of things?

Thxs

[kirankumar]

Private Sub Command1_Click()
Data1.RecordSource = "SELECT * FROM dates WHERE Date BETWEEN #" & Text1.Text & "# AND #" & Text2.Text & "# order by date "
Data1.Refresh
End Sub

this gives the dates between two dates in an order.

Data1.RecordSource = "SELECT * FROM dates order by year(Date), month(Date), day(Date)"

In this inspite of
year(Date), month(Date), day(Date) you can just use order by Date . It also gives the same result you want.

[spud]

This works great, it also reduced my code by a page! thx again