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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicIT ArchitectCommented:
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
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Haris DulicIT ArchitectCommented:
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 DulicIT ArchitectCommented:
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 DulicIT ArchitectCommented:
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 DulicIT ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.