count weeks automatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am using access 97 and I have tables which have dates in the following
format mmm/yyyy. I was just curious to find out if there is a way to work out
the number of weeks based on the above mentioned date format?

so output similar to the followin would be a bonus........

DATE No Of Weeks
APR 2002 4

Thanks
 
You need to define what you meant by No of Weeks in this case.

April has 30 days which is 4 weeks + 2/7 weeks.
 
Every month has between 4 weeks and 4 weeks, 3 days. If all you want is an
integer value (like in your example), it's going to be 4 in every case!

When you say you have dates with that format, are they date/time fields, or
text fields?

It's easy with date/time fields:

SELECT MyDate, Day(DateSerial(Year([MyDate]), Month([MyDate]) + 1, 0)) / 7
AS "No of Weeks"
FROM MyTable

The expression DateSerial(Year([MyDate]), Month([MyDate]) + 1, 0) returns
the last day of the month, the Day function returns strictly that value
(i.e. 31 for Jan, 28 or 29 for Feb, 31 for Mar and so on). Dividing by 7
gives you the number of weeks.
 
Back
Top