ORDER BY on part of a field

Hi,

I'm opening an access db and I'm ordering by a given field but the field is of type Date, is there a way to extract part of the date and order by that?

Thanks,
Uni
LVL 3
Unimatrix_001Asked:
Who is Participating?
 
Haris DulicConnect With a Mentor Commented:
this is how i did it...

rs.Open ("SELECT * FROM TEMP order by format(insert, 'dd') desc"), cnt, adOpenKeyset, adLockReadOnly


could you try this....

 
 
From VB:
Set myTable = databaseConnection.OpenRecordset("SELECT * FROM SomeTable ORDER BY FORMAT(DayTimeField, 'dd')")

Open in new window

0
 
Haris DulicCommented:
you can use format function....

Format(expression[, format[, firstdayofweek[, firstweekofyear]]])...

use access help for complete set of formats that you can use....


example WILL SORT IT BY DD WHICH IS 1-31...

SELECT *
FROM TEMP
order by format(DATE, "dd");
0
 
hieloCommented:
try something like:
... ORDER BY Month(FieldName)
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Unimatrix_001Author Commented:
See attached code for how I'm using it in VB. I'd like to only order by the Day from the DayTimeField.

From VB:
Set myTable = databaseConnection.OpenRecordset("SELECT * FROM SomeTable ORDER BY DayTimeField")

Open in new window

0
 
Haris DulicCommented:
try this....
From VB:
Set myTable = databaseConnection.OpenRecordset("SELECT * FROM SomeTable ORDER BY FORMAT(DayTimeField, "DD")")

Open in new window

0
 
Unimatrix_001Author Commented:
No, I can't do that because then it is classed as two seperate strings with DD being an invalid parameter.
0
 
Haris DulicCommented:
sorry it should be small dd
  	
 
From VB:
Set myTable = databaseConnection.OpenRecordset("SELECT * FROM SomeTable ORDER BY FORMAT(DayTimeField, "dd")")

Open in new window

0
 
Unimatrix_001Author Commented:
That won't help, dd is still not a valid parameter. When I open the recordset I need to pass in a whole string. Are you sure I can use the Format method inside an SQL string because from the (little) I know of SQL, Format is a VB keyword not an SQL keyword.
0
 
Haris DulicCommented:
you said you are querying access db...

when i run this query in access i get results...

here is the query i run :

SELECT * FROM TEMP order by format(insert, "dd") desc ;


0
 
Unimatrix_001Author Commented:
Can you copy and paste the entire function please, because I see no way what you're proposing to work.

Thanks,
Uni
0
 
hieloCommented:
try:
Set myTable = databaseConnection.OpenRecordset("SELECT * FROM SomeTable ORDER BY DAY(DayTimeField)")
0
 
Unimatrix_001Author Commented:
hielo: Nope, no luck. Samo4fun, your solution half works, although it needs to be ddd not dd. Comment accepted nevertheless.
0
 
Unimatrix_001Author Commented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.