How do I order by month in SQL or Access?

[timswim78]

Here is the portion of my query, as you can see I wish to sort by Career Center, then Assessment Date, then by Last Name, then by First Name. However, I would like to order the Assessment Dates by month. Assesment date is a standard DATETIME field.

ORDER BY CareerCenter.CareerCenter, tblSubstanceAbuse.AssesmentDate, tblClient.LastName, tblClient.FirstName;

 

[EagleKeeper]

Originally posted by: timswim78
Here is the portion of my query, as you can see I wish to sort by Career Center, then Assessment Date, then by Last Name, then by First Name. However, I would like to order the Assessment Dates by month. Assesment date is a standard DATETIME field.

ORDER BY CareerCenter.CareerCenter, tblSubstanceAbuse.AssesmentDate, tblClient.LastName, tblClient.FirstName;

There is a function in Access to extract the month from a date field.

 

[timswim78]

Originally posted by: EagleKeeper

Originally posted by: timswim78
Here is the portion of my query, as you can see I wish to sort by Career Center, then Assessment Date, then by Last Name, then by First Name. However, I would like to order the Assessment Dates by month. Assesment date is a standard DATETIME field.

ORDER BY CareerCenter.CareerCenter, tblSubstanceAbuse.AssesmentDate, tblClient.LastName, tblClient.FirstName;

There is a function in Access to extract the month from a date field.

In design view, I selected the field, and went to view - properties, I entered mmm-yyyy as the format. Is this what you are referring to?

 

[EagleKeeper]

Sample of a query that I had used extracting data from a table Sponsor by Date. It was sorted by yyymm - day was set to the first day of the month.

Between DateSerial(Year([Sponsor]![Approval_Date]),Month([Sponsor]![Approval_Date]),1) And (DateSerial(Year([Sponsor]![Approval_Date]),Month([Sponsor]![Approval_Date])+1,1)-1)