MS Access Need Help with calculating dates

Hi

Hope someone can help -- I'm finding this very tricky.

I'm working on a school database - this school needs to pay student host families each month. The students stay with the families for different periods. In my student table I have start date and end date fields which correspond to the family stays. However, I just want the families to be paid for the current month -- the family may have had a student for as much as one year or as little as one week -- so while the start date and end date fields give me info on the total lenght of stay - how do I extract just those families who have students for the current month ?

At the moment I export to excel and enter the current month's date but I'd be much happier if I could do it in access.

Hope it's clear.


TIA

OLIVER

re: MS Access Need Help with calculating dates

Hi,
You should use the function
DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

So in your queryyou add the next columns:

Start_month: datepart("m",[Start_date])
End_month:datepart("m",[End_date])

in criteria U type:

Under start month
Datepart("m",Now())

Or
Under end_month
Datepart("m",Now())

So I think this can help you to get information for the current month!