September 1999
ACCESS

Determine the Week-Ending Value of Any Date

As the Y2K bug has proven, dates are a key factor in most programs--and Access is no exception. Knowing how to manipulate and store dates is an important skill that you'll use over and over again. Unfortunately, the dates a business solution requires aren't always readily available in Access.

Determining the week-ending value of a date is one such necessity. For example, you often need week-ending dates in billing and invoice tracking databases, where you're billing time and labor for certain pay periods. Or, perhaps you want a report that calculates total project hours for a specific week, like the one shown in Figure A. Fortunately, you can determine week-ending values with help from a few Access date functions. In this article, we'll show you how.

Figure A: We'll show you a concise, easy expression that calculates a date's week-ending value.
[ Figure A ]

The technique

To determine the week-ending value from any date, we'll create an expression that uses two common Access functions, Weekday and DateAdd. First, we'll use the Weekday function to determine how many days removed a specific date is from the end of the week. Then, we'll add that number to the date value to get the week-ending date. Before we begin, however, let's take a look at the two functions we'll be using.

Two date manipulation functions

You use the Weekday function to determine which day a specific date falls on. This function returns an integer, 1 to 7, that represents the day of the week--1 for Sunday, 2 for Monday, and so on. So, to determine which day #6/15/1999# falls on, you'd use syntax similar to 

Weekday(#6/15/1999#)
which returns 3, or Tuesday. In addition to a regular value, you can also use a date field in the function. So, assuming the field DateField contains the value #6/15/1999#, then 
Weekday([DateField])
would also return the number 3. Once we determine the number of days between the end of the week and the current date, we'll use the DateAdd function to add those days. This function adds a specified time interval to the given date. It follows this syntax 
DateAdd(interval, number, date)
where interval is the time interval by which you want to increment the date, number is the number you want to add to the date, and date is any valid date or field that contains a date. The time interval can be days, months, years, or any one of the other intervals shown in Table A.

Table A: DateAdd interval settings
Setting Interval
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

So, to add 3 days to #6/15/1999#, you'd use an expression like 

DateAdd("d", 3, #6/15/1999#)
which would return 6/18/1999. Notice that we surrounded the time interval setting with quotes. The function's syntax requires you to do so. Now that we've taken a look at the relevant functions, let's put them together to generate the week-ending dates.

The week-ending date expression

As we mentioned earlier, we first want to determine how many days removed a specific date is from the end of the week. To generate this number, you simply subtract the current date's Weekday number from 7--the maximum number of days in a week. This expression might look like 

7 - Weekday(#6/15/1999#)
which would return 7-3, or 4. Next, we want to add 4 to the original date, so we'll combine the previous expression with the DateAdd function, as in 
DateAdd("d", 7 - Weekday(#6/15/1999#), _
 #6/15/1999#)
which, in this case, would add four days to 6/15/19, returning a week-ending date of 6/19/1999--a Saturday. Now that we've seen how to generate the week-ending date, let's use it on a report that displays project work hours by week.

Create the invoice

We'll base our report on the tblProjectHours table shown in Figure B.

Figure B: This table will contain project IDs, the work date, and the hours spent on each project.
[ Figure B ]

As you can see, the table has three fields: Text, Date/Time, and Number. Both the Project field and WorkDate field are indexed with duplicates allowed. The table doesn't have a Primary Key. When you've finished creating the table, click the Save button and name the table tblProjectHours. Now, let's enter some sample data in the table. To do so, click the Datasheet View button and enter the records shown in Figure C. After you've finished, we'll create the report.

Figure C: We'll create a report based on this table that displays the week-ending dates for these project hours.
[ Figure C ]

Build the project hours report

To begin, close tblProjectHours and select Report from the New Object button's dropdown menu. Double-click on Design View to open a blank report. For our first step, let's add two group levels based on the WorkDate field. To do so, click the Sorting And Grouping button on the toolbar. In the resulting dialog box, select WorkDate for the first group level. Choose Yes for both the Group Header and Group Footer fields, and then select Week from the Group On field's dropdown list.

Now, for the second group level, choose WorkDate once more. This time, though, only choose to show the Group Header section. When you've finished, the dialog box should look like the one shown in Figure D. Finally, close the dialog box and return to the report's Design view.

Figure D: We chose two group levels for the report.
[ Figure D ]

Next, we'll add the report's controls. To start, we'll add the report's title. Click the Label button on the Toolbox and drop a label in the Page Header section. Enter Total Project Hours as the title, select 18 point for the font size, and then click the Bold button on the toolbar. If necessary, resize the label to approximately 2.5 inches wide.

Next, add a textbox to the top WorkDate header. If Access adds a label, simply delete it. Now, click once in the textbox and enter 

="For week ending: " & _
DateAdd("d",7-Weekday([WorkDate]), _ 
[WorkDate])
all on one line. Change the textbox's font point size to 10, and click the Bold button on the toolbar. At this point, your report's design should look like the one shown in Figure E.

Figure E: We added our week-ending expression to the top WorkDate header.
[ Figure E ]

Add the remaining controls

To continue, first click the Field List button on the toolbar to display the available fields. Now, drag WorkDate from the field list to the second WorkDate Header section. Delete the label, if necessary, and click the Bold button.

Next, drag the Project and Hours fields into the Detail section. Resize the Project textbox to 4 characters and the Hours textbox to 9 characters. (When you click on a control's resizing handle, Access displays how many characters the control will display in the program window's status bar.)

Now, select the Project label and the Hours label, and then Cut and Paste them below the week-ending textbox in the top WorkDate header. (If Access didn't generate labels for these two fields, drop in two of your own.) Next, we'll add a control that adds the total hours for each week.

To do so, add a textbox control to the WorkDate Footer section and enter the following expression as its Control Source: 

=Sum(Hours)
Click the Bold button to apply bold formatting to this textbox. Finally, enter Total Hours: as the associated label's text and leave its point size as 8. Figure F shows our completed Design view.

Figure F: We added two dividing lines to complete our report's Design view.
[ Figure F ]

Use it as a guide to align the controls on your report. Notice that we also added two dividing lines, one in the Page Header and one in the WorkDate Footer to separate the weekly groups.

To see the results of our week-ending expression, click the Report View button on the toolbar. When you do, Access displays the report with week-ending dates, as shown in Figure A.

Week-ending dates for Friday

While our expression calculates week-ending dates that fall on Saturday, you can easily modify it so it displays Friday instead. To do so, change the 

7 - Weekday(WorkDate)
portion of the expression to 
6 - Weekday(WorkDate)
When you do, the expression still groups Saturday with the previous week, but now the expression returns Friday's date instead of Saturday's.

Copyright © 1999, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.