I have a database where the records are for members who have to attend a number of events each 12 months. The start date for each 12 month period depends on their job tile. Thus members with job title "A" will start their event year on say 30 October in each year. Members with a different job title might start their event year on the 30 September of each year.

Each members record has a field (which is a look up) called 'YearStart' which is a text field and has an input mask _/_/. A typical entry would be 30/10/.

There is a field for the date of the Event ('EventDate')which is a Date field.

I require to produce a query which shows all the events attended by a Member within his current year. So, if the Members YearStart is 30/10/, and the date the report is required is November 2003, the query will need to identify those courses the Member has attended between 30/10/2003 and 30/10/2004. Can anyone help?

I would not use YearStar you used. I would have two fields: intStartMonth and intStartDay (both are type Integer). For each title I would set both of them, e.g. intStartMonth = 10, intStartDay = 30.

Then to make the comparison use these in a query or in code:

dtmStartDate = DateSerial(Year(Date()), intStartMonth, intStartDate)
dtmEndDate = DateAdd("yyyy", 1, dtmStartDate)

I assumed you always want to check during the same year you run the query, e.g. Year(Date()). If not, change that to the correct year.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thank you SerranoG - I have changed the structure of the member's records so that I can use your suggestions. I have decided to use code rather than a query, and as the check date will not always be the same as the start date I have tested for that by ensuring that the start date year is not greater than the current year - if so the start date year is simply reduced by 1.

Thanks for your help.