Stuck on a date issue

Hi all, and thanks in advance.

I have dates entered in mm/dd/yyyy format but want to sort disregarding the
year... in other words, the following fields would sort this way:

03/07/1972
04/01/1930
07/12/1947

Any way I slice & dice it, I can't figure this 'un out.

--
Thanks for your time!

SELECT *
FROM somewhere
ORDER BY Month(yourDateField), Day(yourDateField)




Vanderghast, Access MVP


"Sue" wrote in message
...
Hi all, and thanks in advance.

I have dates entered in mm/dd/yyyy format but want to sort disregarding
the
year... in other words, the following fields would sort this way:

03/07/1972
04/01/1930
07/12/1947

Any way I slice & dice it, I can't figure this 'un out.

--
Thanks for your time!

Add another field in your query.
DayMonthSort:Format$([YourDateField],"dd")
This will only show the day and then you can change the sort to asending.
--
Please remember to mark this as answered if this solves your problem.


"Sue" wrote:

Hi all, and thanks in advance.

I have dates entered in mm/dd/yyyy format but want to sort disregarding the
year... in other words, the following fields would sort this way:

03/07/1972
04/01/1930
07/12/1947

Any way I slice & dice it, I can't figure this 'un out.

--
Thanks for your time!

Will this include the month? If so, I truly don't understand!
--
Thanks for your time!


"Ryan" wrote:

Add another field in your query.
DayMonthSort:Format$([YourDateField],"dd")
This will only show the day and then you can change the sort to asending.
--
Please remember to mark this as answered if this solves your problem.


"Sue" wrote:

Hi all, and thanks in advance.

I have dates entered in mm/dd/yyyy format but want to sort disregarding the
year... in other words, the following fields would sort this way:

03/07/1972
04/01/1930
07/12/1947

Any way I slice & dice it, I can't figure this 'un out.

--
Thanks for your time!

Sue wrote:
Hi all, and thanks in advance.

I have dates entered in mm/dd/yyyy format but want to sort
disregarding the year... in other words, the following fields would
sort this way:

03/07/1972
04/01/1930
07/12/1947

Quick terminology nitpick (sorry): fields (columns) go across, records
(rows) go down. So I am assuming you meant that you want data in these rows:
07/12/1947
03/07/1972
04/01/1930
to be ordered like this:
03/07/1972
04/01/1930
07/12/1947


Any way I slice & dice it, I can't figure this 'un out.

Is this a Text field or a Date/Time field? If the latter, the Format is
irrelevant: Date/Time values are stored without format. The Format property
is only applied when the values are displayed.

Assuming you have a Date/Time field, switch your query to SQL View (using
the View menu, or the toolbar button, or the right-click menu) and add the
ORDER BY clause like this:

select ...
from ...
where ...
ORDER BY Month([yourfield]),Day([yourfield])

If it's a Text field, then you need to initially convert to date/time:

ORDER BY Month(CDate([yourfield])),Day(CDate([yourfield]))



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Well, if you use the grid, add one computed expression:

Month( yourDateField)

and add another computed expression:

Day( yourDateField)


then spedify you want the ordering be done (ascending) under these two
expression. You are not obliged to SHOW these computed expressions.



Vanderghast, Access MVP



"Sue" wrote in message
...
Will this include the month? If so, I truly don't understand!
--
Thanks for your time!


"Ryan" wrote:

Add another field in your query.
DayMonthSort:Format$([YourDateField],"dd")
This will only show the day and then you can change the sort to asending.
--
Please remember to mark this as answered if this solves your problem.


"Sue" wrote:

Hi all, and thanks in advance.

I have dates entered in mm/dd/yyyy format but want to sort disregarding
the
year... in other words, the following fields would sort this way:

03/07/1972
04/01/1930
07/12/1947

Any way I slice & dice it, I can't figure this 'un out.

--
Thanks for your time!

You steered me right - I just added mm/ to the "dd"
--
Thanks for your time!


"Ryan" wrote:

Add another field in your query.
DayMonthSort:Format$([YourDateField],"dd")
This will only show the day and then you can change the sort to asending.
--
Please remember to mark this as answered if this solves your problem.


"Sue" wrote:

Hi all, and thanks in advance.

I have dates entered in mm/dd/yyyy format but want to sort disregarding the
year... in other words, the following fields would sort this way:

03/07/1972
04/01/1930
07/12/1947

Any way I slice & dice it, I can't figure this 'un out.

--
Thanks for your time!

You can add the month, but I was suggesting that you not show that field and
just use it to sort the data.
--
Please remember to mark this as answered if this solves your problem.


"Sue" wrote:

Will this include the month? If so, I truly don't understand!
--
Thanks for your time!


"Ryan" wrote:

Add another field in your query.
DayMonthSort:Format$([YourDateField],"dd")
This will only show the day and then you can change the sort to asending.
--
Please remember to mark this as answered if this solves your problem.


"Sue" wrote:

Hi all, and thanks in advance.

I have dates entered in mm/dd/yyyy format but want to sort disregarding the
year... in other words, the following fields would sort this way:

03/07/1972
04/01/1930
07/12/1947

Any way I slice & dice it, I can't figure this 'un out.

--
Thanks for your time!