Week Ending Dates

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

Guest

I have a Access 2000 db in the United States that contains the field
InputDate in a mm/dd/yyyy format. It also has fields Department (1) and
Operation (2) with a AccuracyRate (???) I then query this information for a
report to get a total Accuracy rate for each Dept and Operation.

I want the report to give me the total Accuracy Rate for each as a
WeekEnding Date.

Input Date Dept Operation Accuracy Rate
12/06/2004 1 2 50
12/07/2004 1 2 30
12/10/2004 1 1 100

i.e. Sunday Starting Week Date is 12/05/2004 and Saturday Weekending Date is
12/11/04. Input dates falling between these two dates should be placed in
the report using the 12/11/04 date with a total Accuracy Rate of 180


Accuracy REPORT Totals
WeekEndingDate Dept Operation Accuracy Rate
12/11/04 1 2 180

I am using the Grouping by month and by week to get them grouped together
however I need to have the actual WEEK ENDING DATE as a line entry in each
record and if I put the WW formating there I only get the actual INPUT DATE
which normally is not the Saturday Week Ending date.

Is there a method in the report to change the INPUT date to the next
SATURDAY Date or WEEK ENDING DATE.

Thanks ahead of time...

Dwight Cornett
-
-
Hansford D. Cornett
 
There is a formula that will pull the Sunday of the current week, regardless
of the day you actually run it. This formula can also be modified to pull
the Saturday of the current week. This formula is something I came up with
out of necessity and has always worked for me, but I'm sure one of the MVP's
will have a solution that's built into Access.

Date() - Weekday(Date()) + 1 will always give you the current week's Sunday
date.

Date() - Weekday(Date()) + 7 will always give you the current week's
Saturday date.

Both work regardless of when you run them and both can be modified to give
you future or past dates.

Date() - Weekday(Date()) + 8 will always give you next Sunday's date for
example.

I hope this is something you find useful.

T
 
That's the way to do it. Just in case anyone cares, it works because the
default weekday numbering in Access is Sunday is 1, Monday is 2 and so on up
to Saturday being 7.

Date() - Weekday(Date()) will, by default, return the date of the Saturday
before the current date.
 
Back
Top