Stuck on a date issue

[Sue]

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.

 

[Michel Walsh]

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




Vanderghast, Access MVP

 

[Ryan]

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.

 

[Sue]

Will this include the month? If so, I truly don't understand!
--
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.

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.

 

[Bob Barrows [MVP]]

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]))

 

[Michel Walsh]

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

Will this include the month? If so, I truly don't understand!
--
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.

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.

 

[Sue]

You steered me right - I just added mm/ to the "dd"
--
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.

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.