Changing the standard "week"

[sbaird]

Aloha from Hawaii,

I'm beating my head on the wall here. I have a recruiting contact
managment database I'm trying to create. Managers (there ar 14 of
them) have to make a certain number of recruiting calls per week, and
so many of them have to result in at least an appointment. However,
the "week" is Wednesday, 12:01pm to the following Wednesday, 12:00noon.
So here are my questions...

1) Is there a way to change the standard week in a database to be Wed -
Wed in both the forms and reports? I need to make a form, and a report,
that basically states "You have a quota of 60 calls per month, and a
minimum weekly call of 13. You've made 10 calls this week, so you have
3 calls remaining". Not quite like that but just a text block that
says "Remaining calls: 3". Just wanted to explain what I'm trying to
display.

2) My main form (fed by tblCONTACTS) has a subform (fed by tblCALLS).
On the side of the form, I want to put three buttons, and have subform
update each time a button is selected:
- Display All calls made to this contact (btnALL)
- Display all calls made to this contact month to date (btnMTD)
- Display all calls made to this contact week to date (btnWEEK),
keeping in mind that our week is considered Wednesday noon to the
following Wednesday at noon.

[Keith Wilby]

<sb****@cbpacific.comwrote in message
news:11**********************@k78g2000cwa.googlegr oups.com...

Aloha from Hawaii,

Greetings from freezing England.

>
1) Is there a way to change the standard week in a database to be Wed -
Wed in both the forms and reports?

The way I do it is to have a lookup table with the week numbers in one field
and their week ending date in another, the week ending days being (in my
case) Thursdays. Here's some code to create a lookup table, you'll have to
tweak the dates of course.

Keith.
www.keithwilby.com

Public Sub libMakeDate()

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fd As DAO.Field
Dim rs As DAO.Recordset
Dim dtmdate As Date

Set db = CurrentDb

' Create a new table
Set td = db.CreateTableDef("tblDate")

' Create a new field
Set fd = New Field
fd.Name = "Date"
fd.Type = dbDate

' Add the field to the table
td.Fields.Append fd

' Create a new field
Set fd = New Field
fd.Name = "WeekNo"
fd.Type = dbText

' Add the field to the table
td.Fields.Append fd

' Add the table to the database
db.TableDefs.Append td

' Open the table
Set rs = td.OpenRecordset

' Add dates and weekno
For dtmdate = #10/27/2002# To #12/31/2010# Step 7
rs.AddNew
rs!Date = dtmdate
rs!WeekNo = Year(dtmdate) Mod 10 & Right("0" & Format(dtmdate,
"ww"), 2)
rs.Update
Next
rs.Close

End Sub

[Kc-Mass]

Take a look at the following access function. It works with the day of week
based on a constant set in your program. So you can change the first day of
the week to Wednesday or any other week day. This is straight from the VBA
help file.

Weekday Function
Returns a Variant (Integer) containing a whole number representing the day
of the week.

Syntax

Weekday(date, [firstdayofweek])

The Weekday function syntax has these named arguments:

Part Description
date Required. Variant, numeric expression, string expression, or any
combination, that can represent a date. If date contains Null, Null is
returned.
firstdayofweek Optional. A constant that specifies the first day of
the week. If not specified, vbSunday is assumed.

Settings

The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
<sb****@cbpacific.comwrote in message
news:11**********************@k78g2000cwa.googlegr oups.com...

Aloha from Hawaii,

I'm beating my head on the wall here. I have a recruiting contact
managment database I'm trying to create. Managers (there ar 14 of
them) have to make a certain number of recruiting calls per week, and
so many of them have to result in at least an appointment. However,
the "week" is Wednesday, 12:01pm to the following Wednesday, 12:00noon.
So here are my questions...

1) Is there a way to change the standard week in a database to be Wed -
Wed in both the forms and reports? I need to make a form, and a report,
that basically states "You have a quota of 60 calls per month, and a
minimum weekly call of 13. You've made 10 calls this week, so you have
3 calls remaining". Not quite like that but just a text block that
says "Remaining calls: 3". Just wanted to explain what I'm trying to
display.

2) My main form (fed by tblCONTACTS) has a subform (fed by tblCALLS).
On the side of the form, I want to put three buttons, and have subform
update each time a button is selected:
- Display All calls made to this contact (btnALL)
- Display all calls made to this contact month to date (btnMTD)
- Display all calls made to this contact week to date (btnWEEK),
keeping in mind that our week is considered Wednesday noon to the
following Wednesday at noon.

[CDMAPoster]

On Jan 25, 3:16 am, [e-mail] wrote:

Aloha from Hawaii,

I'm beating my head on the wall here. I have a recruiting contact
managment database I'm trying to create. Managers (there ar 14 of
them) have to make a certain number of recruiting calls per week, and
so many of them have to result in at least an appointment. However,
the "week" is Wednesday, 12:01pm to the following Wednesday, 12:00noon.
So here are my questions...

1) Is there a way to change the standard week in a database to be Wed -
Wed in both the forms and reports? I need to make a form, and a report,
that basically states "You have a quota of 60 calls per month, and a
minimum weekly call of 13. You've made 10 calls this week, so you have
3 calls remaining". Not quite like that but just a text block that
says "Remaining calls: 3". Just wanted to explain what I'm trying to
display.

2) My main form (fed by tblCONTACTS) has a subform (fed by tblCALLS).
On the side of the form, I want to put three buttons, and have subform
update each time a button is selected:
- Display All calls made to this contact (btnALL)
- Display all calls made to this contact month to date (btnMTD)
- Display all calls made to this contact week to date (btnWEEK),
keeping in mind that our week is considered Wednesday noon to the
following Wednesday at noon.

WeekNumber = DatePart("ww", TimeStamp, 4) - Abs(TimeValue(TimeStamp) <=
TimeSerial(12, 0, 0) And Weekday(TimeStamp) = 4)

This takes the week number given by Access for weeks starting on
Wednesdays and subtracts one if the time is before or equal to
Wednesday noon.

Test results:

tblTemp
TimeStamp Date/Time
1/25/2007 12:15:00 PM
1/2/2007 11:45:00 AM
1/3/2007 11:45:00 AM
1/3/2007 12:15:00 PM
1/10/2007 12:00:59 PM
1/10/2007 12:01:00 PM
2/14/2007 8:55:00 AM
1/1/2014 11:45:00 AM
1/1/2014 12:45:00 PM
1/2/2008 11:45:00 AM

SELECT DatePart("ww", TimeStamp, 4) - Abs(TimeValue(TimeStamp) <
TimeSerial(12, 1, 0) And Weekday(TimeStamp) = 4) AS WeekNumber FROM
tblTemp;

WeekNumber
5
1
1
2
2
3
7
0
1
1

If the first day of the year is a Wednesday, Wednesday morning (plus
noon) will be considered week 0. If the first day of the year is on a
Tuesday, Wednesday morning (plus noon) will be considered as part of
week 1. Also note that the DatePart function sometimes has problems
when a firstweekofyear (the second optional parameter) value of
vbFirstFourDays (2) is used. That logic gets a little complicated so I
feel a little sympathy for Microsoft about not getting everything
perfect the first time. The date values shown here are the only ones I
tested. I hope this helps.

James A. Fortune
CD********@FortuneJames.com