Week, monthly, quarterly

  • Thread starter Thread starter Ranjith Kurian
  • Start date Start date
R

Ranjith Kurian

I have a table called as Task; this table has four columns Name, Task,
Frequency and Week.
Name: employee name
Task: Assigned work to each employee
Frequency and Week: the due day to perform the assigned task

I would like to create a query,if any task is pending on that particular day
it should be viewed

Frequency column:
Daily: if I have mentioned daily for any task then it should be viewed on
everyday as pending task.
If it’s weekly then every week it should be viewed as pending,
simultaneously it should check Week column to know which day of the week it
should be pending



Name Task Frequency Week
OP A Daily Daily
OP B Weekly Monday
OP C Weekly Thursday
OM D Weekly Friday
OM E Weekly Wednesday
OM F Monthly 2nd Week of everymonth(1stday)
OX G Monthly every 1st day of the month
OX H Quartely 3rd month 1st day
OX I Quartely 3rd month 15th day

For example: if today is Monday 01/01/2010 then from the above table below
result needed

Name Task Frequency Week
OP A Daily Daily
OP B Weekly Monday
OX G Monthly every 1st day of the month
 
I have a table called as Task; this table has four columns Name, Task,
Frequency and Week.
Name: employee name
Task: Assigned work to each employee
Frequency and Week: the due day to perform the assigned task

I would like to create a query,if any task is pending on that particular day
it should be viewed

Frequency column:
Daily: if I have mentioned daily for any task then it should be viewed on
everyday as pending task.
If it’s weekly then every week it should be viewed as pending,
simultaneously it should check Week column to know which day of the week it
should be pending



Name Task Frequency Week
OP A Daily Daily
OP B Weekly Monday
OP C Weekly Thursday
OM D Weekly Friday
OM E Weekly Wednesday
OM F Monthly 2nd Week of everymonth(1stday)
OX G Monthly every 1st day of the month
OX H Quartely 3rd month 1st day
OX I Quartely 3rd month 15th day

For example: if today is Monday 01/01/2010 then from the above table below
result needed

Name Task Frequency Week
OP A Daily Daily
OP B Weekly Monday
OX G Monthly every 1st day of the month
Click to expand...

You'll need some VBA code using the DateAdd and DateSerial functions to parse
through this Frequency and Week field to construct an actual date/time value.
Since the "Week" field is far more human-readable than computer-readable this
may be a real chore. Any chance that you could construct a table of dates
instead?
 
John Would this be of any help? From project long forgotten.

Sub test()
Dim dtDate As Date
dtDate = #12/15/2008#
Dim DateText As String
Do While dtDate < #1/18/2010#
DateText = WeekofYear(dtDate)
Debug.Print dtDate; " "; Format(dtDate, "dddd"); " "; DateText
dtDate = dtDate + 7
Loop
End Sub


Function WeekofYear(TheDate As Date)
Dim strYearPart As String
Dim strWeekPart As String
If Format(TheDate, "ww", 7, 2) = "53" And Year(TheDate) <> Year(TheDate -
7) Then
strYearPart = CStr(CInt(Year(TheDate) - 1))
Else
strYearPart = Format(TheDate, "yyyy", 7, 2)
End If
strWeekPart = IIf(Len(Format(TheDate, "ww", 7, 2)) = 1, "0" &
Format(TheDate, "ww", 7, 2), Format(TheDate, "ww", 7, 2))
WeekofYear = strYearPart & "-" & strWeekPart
End Function


It produces output like this:
1/5/2009 Monday 2009-01
1/12/2009 Monday 2009-02
1/19/2009 Monday 2009-03
1/26/2009 Monday 2009-04
2/2/2009 Monday 2009-05
2/9/2009 Monday 2009-06
2/16/2009 Monday 2009-07
2/23/2009 Monday 2009-08
3/2/2009 Monday 2009-09
3/9/2009 Monday 2009-10
3/16/2009 Monday 2009-11
3/23/2009 Monday 2009-12
3/30/2009 Monday 2009-13
4/6/2009 Monday 2009-14
4/13/2009 Monday 2009-15
4/20/2009 Monday 2009-16
4/27/2009 Monday 2009-17
5/4/2009 Monday 2009-18
5/11/2009 Monday 2009-19
5/18/2009 Monday 2009-20
5/25/2009 Monday 2009-21
6/1/2009 Monday 2009-22
6/8/2009 Monday 2009-23
6/15/2009 Monday 2009-24
6/22/2009 Monday 2009-25
6/29/2009 Monday 2009-26
7/6/2009 Monday 2009-27
7/13/2009 Monday 2009-28
7/20/2009 Monday 2009-29
7/27/2009 Monday 2009-30
8/3/2009 Monday 2009-31
8/10/2009 Monday 2009-32
8/17/2009 Monday 2009-33
8/24/2009 Monday 2009-34
8/31/2009 Monday 2009-35
9/7/2009 Monday 2009-36
9/14/2009 Monday 2009-37
9/21/2009 Monday 2009-38
9/28/2009 Monday 2009-39
10/5/2009 Monday 2009-40
10/12/2009 Monday 2009-41

Regards

Kevin
 
Back
Top