I'm not sure why I can't think of how to fix this, but I'm missing something

I have a table with a column for 'birthdays' The format is mm/dd, and a form for the users to put in their birthday.

When you look at the table, it may look like 05/12. But if you click on the cell it's shows it as 05/12/2007.

The problem I'm running into, I have a Data Access Page that displays all the birthdays grouped by month. But for some reason all the entries from 2006 are grouped by month, but then all the entries we've made in 2007 are grouped together by themselves.

so we have
(all the entries we made in 2006)
June
July

etc

but then at the end of the list
we have again
June
July
etc (these are the ones we entered in 2007)

Is there a way to store a date as only mm/dd?
Or is there a way to make the DAP group these by month, and ignore the year?

A suggestion would be to not store it as a date, store it as text and it won't have that issue.

Would I still be able to have headers for the months, on my DAP?

January

February

March.....

You could convert it back to a date in the control for that purpose and use the format function to format it to month "mmmm"

or maybe use something like
CStr(Format([Datefield],"mmmm")) as your query field that you group by.

or maybe use something like
CStr(Format([Datefield],"mmmm")) as your query field that you group by.

If you group by that though the sorting order will be thrown off by the words of the months. Group by the number and DISPLAY it with the name formatting.

I hadn't thought about order.

You could also have it both ways using something like:

CStr(Format([Datefield],"mm"))
and
Format([Datefield],"mmmm")
..as two fields in your query.

Then use Group By and sort ascending for the former, use First or Last on the latter, and Count for the number of birthdays.

This way you don't need to store the date as text and therefore have to convert your text dates to 'date' anytime you reference the stored date information.

Well I just looked at it again, and remembered that originally that was a text field, that I converted to a date field.

When I converted it, that's when the year 2006 must have been added. I rechecked the ones I entered today, and they were entering correctly (mm/dd)

So I just removed the 2006 from all the old ones, and I think it's all fixed!

Thanks everyone!