Sort by Month with a date 3

[StayAtHomeMom]

Hello,
I have a query with names and birthdates in the MMDDYYYY format. I would like to sort them by month, but I don't know what to put in the criteria box for that field to do that. I looked in Access Help, but found nothing. It seems simple, but I cannot think of how to do it.

Thanks for your help.

 

[JonFer]

If the birthday is a date in the table, then use:

Select Person,
Format(BirthDate,"mmddyyyy") as BirthDay
From YourTable
Order By Format(BirthDate,"mmddyyyy")

I assume you are currently just displaying the birthday in that format without using the Format() function or else you'd be able to sort on it already.


John

http://www.interautomateconsulting.com

 

[BobAtHome]

Assume that the field that holds the birth date is called "BirthDate".

In the query, in the last column, paste the line below in the "field" row.

showmonth: Month([Birthdate])

Leave the "table" row empty.
Select either ascending or descending in the "sort" row.
Deselect the check box in the "show" row if you don't want to see the month.

This will sort the query result by the month of each birth date.

 

[THWatson]

StayAtHomeMom
Here's one approach...

Click the Sigma sign to make your query a Totals query. Add another column to your query. Assuming that the name of your birthdate column is birthdate, use this expression...

Sort: Month([birthdate])

Then sort ascending.

Tom

 

[kvest]

BobatHome,

Thanks for the information on this post. It did exactly what I needed, however, on the report I based off this query, the month (which I want displayed) shows up as a number. How can I convert that back to the month name so that it prints correctly?

Thanks

 

[PHV]

Take a look at the MonthName function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kvest]

Thanks PHV, but I am very new at this....I have several books on the way that will hopefully assist. I have been unsuccessful in getting this to work and a search of that command reveals various levels of success.

Would I add another column to the query that contains the Month command or modify it into MonthName? Sorry if this is very basic questions...I am begining to think I am in over my head with this project.

Thanks in advance....

 

[PHV]

Another way is the Format function with 'mmmm' as 2nd parameter.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244