Group on odd dates (Access 2000)

[sanora]

In a report, I would like to be able to group events by specified dates. I can group by months or by quarters. Can I <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> group events that fall between the first and the 15th of the month and the following group would be events on the 16th through the end of the month? Perhaps paydays are second and fourth Fridays and I want to use those days as group headers. Are these things possible?

Thanks.

[charlotte]

You would do it by creating a calculated field in the underlying query and using that to return an arbitrary value (i.e., if the day of the date is between 1 and 15, return a 1 and if it's > 15 return a 2). Then group first by month and then by the calculated field.

[HansV]

<P ID="edit" class=small>Edited by HansV on 08-Aug-02 10:50.</P>I have attached a zipped Access 97 database that demonstrates how to group on half months (Charlotte explained how to do this) and to display the second/fourth Friday in the month in the group header.

The database contains a module with a custom function get the n-th mmm-day in the month. This custom function is used both in a sample query and in a sample report.

Edited by HansV because the use of three times the 24th letter of the alphabet caused a mail server to bounce the digest.