[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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
0
Unimatrix_001
Asked:
Unimatrix_001
  • 6
  • 5
  • 2
1 Solution
 
Haris DjulicCommented:
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Haris DjulicCommented:
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 DjulicCommented:
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 DjulicCommented:
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
 
Haris DjulicCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now