Sorting database by date

[Sandra]

I have Access 2007 Trial version and am trying to sort my database by date of
birth column. I can get it to do it by year, but I need to know by month,
ie. all birthdays in July, etc. The year is irrelevant but we like to keep a
track if it is an important milestone. Can this be done or am I being too
difficult.

 

[Larry Daugherty]

Create a new column in the query where you are currently sorting

Month: =format([YourDateColumn], "m")

sort Ascending and you don't need to return the month value in your
result set unless you wish to do so.

HTH

 

[Sandra]

Thank you, this helped heaps.

Create a new column in the query where you are currently sorting

Month: =format([YourDateColumn], "m")

sort Ascending and you don't need to return the month value in your
result set unless you wish to do so.

HTH
--
-Larry-
--

I have Access 2007 Trial version and am trying to sort my database by date of
birth column. I can get it to do it by year, but I need to know by month,
ie. all birthdays in July, etc. The year is irrelevant but we like to keep a
track if it is an important milestone. Can this be done or am I being too
difficult.

 

[Douglas J. Steele]

Actually, since Format returns a string, that will result in the birthdays
being sorted Jan, Oct, Nov, Dec, Feb, Mar, ... (i.e. 1, 10, 11, 12, 2, 3,
....)

You could either use Format([YourDateColumn], "mm") (which will return 01,
02, 03, ...), or DatePart("m", [YourDateColumn]) (since DatePart returns a
number, not a string)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Create a new column in the query where you are currently sorting

Month: =format([YourDateColumn], "m")

sort Ascending and you don't need to return the month value in your
result set unless you wish to do so.

HTH
--
-Larry-
--

I have Access 2007 Trial version and am trying to sort my database by date of
birth column. I can get it to do it by year, but I need to know by month,
ie. all birthdays in July, etc. The year is irrelevant but we like to keep a
track if it is an important milestone. Can this be done or am I being too
difficult.

 

[Larry Daugherty]

Thank you Doublas, you're absolutely right.

Some days I should quit earlier. I was going to use "mm", then
realized that would force the leading zero so I dropped an "m" without
thinking it through.

OP can format again to get rid of the leading zero if it's a problem.

--
-Larry-
--

Actually, since Format returns a string, that will result in the birthdays
being sorted Jan, Oct, Nov, Dec, Feb, Mar, ... (i.e. 1, 10, 11, 12, 2, 3,
...)

You could either use Format([YourDateColumn], "mm") (which will return 01,
02, 03, ...), or DatePart("m", [YourDateColumn]) (since DatePart returns a
number, not a string)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Create a new column in the query where you are currently sorting

Month: =format([YourDateColumn], "m")

sort Ascending and you don't need to return the month value in your
result set unless you wish to do so.

HTH
--
-Larry-
--

I have Access 2007 Trial version and am trying to sort my

database
by date of

birth column. I can get it to do it by year, but I need to know

by
month,

ie. all birthdays in July, etc. The year is irrelevant but we

like
to keep a

track if it is an important milestone. Can this be done or am I being too
difficult.