sort by date

[jrthor2]

I have an Access 200 database of new members for our church. When you put in a new member, you put in the dates as xx/xx/xxxx, all one field. What I need to do is be able to sort by the year part of the date, so it lists the most recent members first. I have the code in there now, but it is not sorting by the year, I think is is sorting by the month, and then the day and then the year. Here is what my code is so far:

If order="MembershipDate" or order2="MembershipDate" Then
strOrderBy = "MembershipDate"
SQLstmt = "SELECT * from New_Members order by MembershipDate Desc"

[Bullschmidt]

SQLstmt = "SELECT * from New_Members order by Right(MembershipDate, 4) Desc, MembershipDate Desc"

[jrthor2]

I would agree, but IN Access 2000, if I set up the field as a Date field, then it becomes a required field, and the field is not necessarily required, so how do I get past that? Also, whe it is a Date field, it only uses 2 digits for my year and I can't get it to use 4 digits. If you could help with that, that would be great.

[jrthor2]

Ok, well, I cam across another issue. when sorting by date, I have:

SQLstmt = "SELECT * from New_Members order by (right(MembershipDate,4)) Desc"

How can I also get it sort it by the Month and Day? My date field is just a text box.