Deriving a Phase and Week number

[Guest]

Hello all,

For each group of participants I have two phases, Orientation and
Probation. I have the Orientation Start Date and the Probation Start Date
stored in a table for each group.

Project completion dates are recorded in a seperate table. As projects
are completed, I need to determine during which phase and (whole) week they
were completed.

Example: Orientation Start Date of 1/1/06, nearest Monday is 1/2/06
Probation Start Date 2/1/06, nearest Monday is 2/6/06
First Project completed 1/6/06 means Orientation Week 1
Second Project completed 1/17/06 means Orientation Week 3
Third Project completed 2/2/06 means Orientation Week 5
Fourth Project completed 2/16/06 means Probation Week 2

Esentially I need to say:
If Completion_Date is < P_Start_Date, then it is Orientation Week X
If Completion_Date is >= P_Start_Date, then it is Probation Week X

I would appreciate any help! Thank you much!!
Renee

 

[Guest]

Hello all,

For each group of participants I have two phases, Orientation and
Probation. I have the Orientation Start Date and the Probation Start Date
stored in a table for each group.

Project completion dates are recorded in a seperate table. As projects
are completed, I need to determine during which phase and (whole) week they
were completed.

Example: Orientation Start Date of 1/1/06, nearest Monday is 1/2/06
Probation Start Date 2/1/06, nearest Monday is 2/6/06
First Project completed 1/6/06 means Orientation Week 1
Second Project completed 1/17/06 means Orientation Week 3
Third Project completed 2/2/06 means Orientation Week 5
Fourth Project completed 2/16/06 means Probation Week 2

Esentially I need to say:
If Completion_Date is < P_Start_Date, then it is Orientation Week X
If Completion_Date is >= P_Start_Date, then it is Probation Week X

I would appreciate any help! Thank you much!!
Renee

Hi Renee,

I have a couple of questions.

Example: Orientation Start Date of 1/1/06, nearest Monday is 1/2/06

By "Nearest Monday", if the Orientation Start Date was 1/4/2006, would the
nearest Monday be 1/2/2006 or 1/9/2006?

Do you want it in a function (like CalcWeek(Pb_Start, Proj_End)) or are you
looking for a formula for a calculated control? (a text box control source of
"=IIF(...)")

 

[Guest]

Thanks for responding Steve,
If the Orientation Start Date was 1/4/2006, the nearest Monday be
1/9/2006. I am currently building this formula in a query.

If for example I used this function to show me all projects completed during
the 2nd week of Orientation, it would have a multiple row result. I would
prefer to use a function and call it in the query; but, I have only worked
with functions that return one row.

I would appreciate any support you have to offer!
Thank you again,
Renee

 

[Guest]

Thanks for responding Steve,
If the Orientation Start Date was 1/4/2006, the nearest Monday be
1/9/2006. I am currently building this formula in a query.

If for example I used this function to show me all projects completed during
the 2nd week of Orientation, it would have a multiple row result. I would
prefer to use a function and call it in the query; but, I have only worked
with functions that return one row.

I would appreciate any support you have to offer!
Thank you again,
Renee

Renee,

It really hepled that you included data and the expected results.

See if this works:

Create a standard module and paste in the following code:


'--------------------------------------------------------------------
'
' Calculates the phase (Orientation or Probation) and
' the week number of a group of participants
'
' Arguments:
' O_Start - Orientation start date as a date
' P_Start - Probation start date as a date
' ProjEnd - Project end date as a date
'
' Returns: a string
'
' Usage:
' As control source:
' using static dates =
fPhaseWeek(#1/1/2006#,#2/1/2006#,#2/17/2006#)
' using variables (fields) =
fPhaseWeek([dteOrient],[dteProb],[dteProjEnd])
'
' In a query:
' PW: fPhaseWeek([dteOrient],[dteProb],[dteProjEnd])
' ( field names ^^ ^^ ^^ )
'--------------------------------------------------------------------

Public Function fPhaseWeek(O_start As Date, P_Start As Date, ProjEnd As
Date) As String
Dim O_Monday As Date
Dim P_Monday As Date

' calc nearest Monday for Orientation ( - might be able to delete this)
If Weekday(O_start, vbMonday) = 1 Then
O_Monday = O_start
Else
O_Monday = O_start + 8 - Weekday(O_start, vbMonday)
End If

' calc nearest Monday for Probation ( - need this)
If Weekday(P_Start, vbMonday) = 1 Then
P_Monday = P_Start
Else
P_Monday = P_Start + 8 - Weekday(P_Start, vbMonday)
End If

'calc the phase and the week number
If ProjEnd < P_Monday Then
fPhaseWeek = "Orientation week " & DatePart("ww", ProjEnd)
Else
fPhaseWeek = "Probation week " & DatePart("ww", ProjEnd) -
DatePart("ww", P_Monday) + 1
End If

End Functio
'--------------------------------------------------------------------------------

Save the module.

In a query, you need an Orientation field, Probation field and a Project end
field.
Then create a calculated field like

PW: fPhaseWeek(Orientation, Probation, ProjectEnd)

(or whatever you field names are)

On the form, the control source for a textbox would be: PW


HTH