Date sorting

[ankrumc]

Hi,

Is there an easy way to sort dates by month in a query? I'm putting in birthdays, and would like to run a report for all the birthdays in the month of January, or Feb etc, but not just in one year, for all of them.

Thanks!

 

[vbaInet]

You sure can. Put something like this in a new column in your query:

MonthSort: Month([YourDateFieldHere])

Sort under MonthSort

 

[ankrumc]

OK, I'm really bad at this.

I copied your code and put it in the field as MonthSort: Month([BDateC1])

But I'm not sure where to do the sort. If you wouldn't mind doing some more hand holding, I could use it. I'm also not great with Macro if that's what that is.

Thanks,
Christian

 

[vbaInet]

Version of Access?

 

[ankrumc]

it is 2007.

 

[vbaInet]

Right click one of the sections of your report and click Sorting and Grouping. A pane would appear at the bottom, click SORTING to add your sort.

 

[ankrumc]

Ok,

So I have them all sorted, but I'd prefer to have just each months birthdays showing at a time, (so a drop down box that lets me select the current months birthdays).

Also, it is for some couples, (bdateC1, bdate C2). Can I somehow manipulate it so that if the husband doesn't have a january birthday, but the wife does, that they still get pulled up in January birthday report?

Thanks a bunch! you're the vbaest!

 

[vbaInet]

So I have them all sorted, but I'd prefer to have just each months birthdays showing at a time, (so a drop down box that lets me select the current months birthdays).

Group By MonthSort, show the footer section of this group, set the Force New Page property of the section to After Section.

Also, it is for some couples, (bdateC1, bdate C2). Can I somehow manipulate it so that if the husband doesn't have a january birthday, but the wife does, that they still get pulled up in January birthday report?

MonthSort: Month(IIF(IsNull([BDateC1]), [BDateC2], [BDateC1]))

 

[ankrumc]

Ok,

So I'd been working with this for a while, and for someone else who stumbles upon this, if you have multiple fields you want to be brought up (2 or more bdays), use

monthsort: Month(yourfieldhere),

and then

monthsort2: month(your2ndfieldhere)

and so on.

 

[boblarson]

Ok,

So I'd been working with this for a while, and for someone else who stumbles upon this, if you have multiple fields you want to be brought up (2 or more bdays), use

monthsort: Month(yourfieldhere),

and then

monthsort2: month(your2ndfieldhere)

and so on.

If you have more than one field for storing birthdays then your table design is incorrect and not normalized. With rare exceptions, you do not use repeating fields.