Number of days in month from a given date

Hi all,

Would any of you know how to create a calculated field in an Access 2003 query to calculate the number of days in a month for a given date.

Field name is "Date"

I need a function or formula. I'm trying to avoid VBA for this. Thanks.

I would build another table with three fields

tblMonth
------------
mID (PK)
MonthID (1-12 representing month)
MonthDays (Actual number of days for the month)

Create a new field that contains the expression: =Month([Date])

Save the query. Create a new query using the existing query as the record source. Join that query to the tblMonth on the field created and MonthID. Add the field MonthDays to the new query.

BTW: Date is a reserved name in Access and really should not be used as field name. It may cause issues down the road.

Thanks alansidman.

I actually found the solution before I saw your reply. I'll try your recommendation and if it works I'll add to the rep. Thanks again.