issue with creating a weekly report

[associates]

Hi,

I desperately need your help here. I'm trying to work out a better way of producing a weekly report for a job that should show the weekly activities for last week and the current week using Access 03.

In the table "weeklytable", i have the following fields
WeeklyID
WeeklyActivities
ProposedActivities
WeekNo
Date
JobNo

Let's say, in the "weeklytable" table for a particular job, we know the date of the last weekly activities and the current date of the week, how do i work out how many weeks apart between them in VBA?

The reason for this is there might be a case where the job manager forgets to put in the report for the current weekly activities. So i need some sort of coding to let him/her know that there is a missing weekly activities.

Please feel free to suggest anything that may make this simple.

Thank you in advance

[MMcCarthy]

You can use the DateDiff() function.

DateDiff(interval, dateValue1, dateValue2, [firstdayofweek], [firstweekofyear])

Returns the difference in the specified interval between two given dates. e.g. DateDiff("yyyy", #01/05/2001#, #02/07/2006#) would return 5.

interval in your case would be "ww" which would return the number of weeks between two given dates.

[firstdayofweek] is optional. It specifies the first day of the week. Access assumes that Sunday is the first day of the week.

[firstweekofyear] is optional. It specifies the first week of the year. Access assumes that the week containing Jan 1st is the first week of the year.