If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Week Ending Dates
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 |
#2
|
|||
|
|||
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 "Hansford cornett" wrote in message ... 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 |
#3
|
|||
|
|||
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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "TPratt" wrote in message ... 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 "Hansford cornett" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Format Crosstab query to show totals in Week Ending groups | KimberlyC | Running & Setting Up Queries | 1 | September 1st, 2004 12:30 AM |
Convert date into week number | Frank van Thiel | Worksheet Functions | 3 | July 6th, 2004 12:23 PM |
grouping dates in current week | Soe | Worksheet Functions | 2 | May 14th, 2004 12:58 PM |