?
Solved

rs.sort by year on date field?

Posted on 2003-02-19
12
Medium Priority
?
320 Views
Last Modified: 2013-12-25
I need to sort a recordset by the year of a date field.

right now I have: rs.sort = "BIRTHDATE DESC"

I have tried rs.sort = "YEAR(BIRTHDATE) DESC" but that doesn't work.  Is there a way to do this?
0
Comment
Question by:sgreenwood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7982692
What is the datatype of BirthDate ?

What is your database engine ?

0
 

Author Comment

by:sgreenwood
ID: 7982859
The database is SQL Server 2000, BirthDate is a nvarchar field, does it need to be a date type to make this work?
0
 

Author Comment

by:sgreenwood
ID: 7982869
The database is SQL Server 2000, BirthDate is a nvarchar field, does it need to be a date type to make this work?
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!

 
LVL 70

Accepted Solution

by:
Éric Moreau earned 200 total points
ID: 7982924
unless your date is formated as yyyy/mm/dd, sort will not work correctly. Change your SQL query to CAST you field as a datetime field.
0
 
LVL 4

Expert Comment

by:bistrica
ID: 7985709
add extra column with year in it and sort it
0
 

Expert Comment

by:SunnyJoker
ID: 7985897
With SQL Server you can use the function datepart(). In a Select-Statement it would look like:

select * from [tablename] order by datepart(year, BIRTHDATE)

You can also use month or day instead of year. I don't know if this works with rs.sort = "datepart(year, BIRTHDATE)". But if you can, always try to filter or sort direct within a Select-Statement and give the result to the recordset.
0
 

Expert Comment

by:SunnyJoker
ID: 7985930
I forgot, like emoreau wrote it's recommendable to have a datetime field. Datepart casts the date in bithdate automatic to datetime, but if this doesn't work because  date in the field is incorrect you will receive an error. So best would be to change the type of the field permanent to a datetime format in the database.
0
 
LVL 3

Expert Comment

by:vbbuff
ID: 7991295
dear sqreenwood

can you try this

 rs.sort = "clng(Cdate(BIRTHDATE)) DESC"
0
 

Author Comment

by:sgreenwood
ID: 7994449
I tried both rs.sort = "datepart(year, BIRTHDATE)" and rs.sort = "clng(Cdate(BIRTHDATE)) DESC" with no luck.  Unfortunately, this recordset is from a stored procedure, and I have been told not to change that or the type of the field.  Thanks for the suggestions!
0
 
LVL 3

Expert Comment

by:vbbuff
ID: 7996845
If you are opening the recordset by the execute method of the command object, then try using the open method of the recordset object.

for eg , If your statement is

set rs = com.execute

then try changing it to:

set rs = new Recordset
rs.cursorlocation = aduseclient
rs.open,com,adopenstatic,adlockreadonly,adcmdstoredProc

rs.sort = "clng(Cdate(BIRTHDATE)) DESC"
0
 
LVL 3

Expert Comment

by:vbbuff
ID: 7996850
sorry it should be:

rs.open  "ProcName",com,adopenstatic,adlockreadonly,adcmdstoredProc
0
 
LVL 1

Expert Comment

by:ayufans
ID: 9018157
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- answered by emoreau

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

ayufans
Cleanup Volunteer
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month11 days, 20 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question