Cycle through a period of weeks...

[dodgyone]

I have created a time sheet DB for my users. They enter their working times in 8 week periods and then they have to start again. So weeks 1 to 8 and loop back to week 1 etc etc.

I'm trying to create some code that tells the user on the main screen form which week they are in for that period and then for it to revert back to week 1 after the 8 week period has ended. Our new time period started this week on 25/03/02 so this is the ideal time to get this cracked!

I have a table with users names and their overall time and I wondered about placing a starting and ending date field in there for each user and then going from there but I'm not too sure how to produce something suitable.

 

[dodgyone]

Sorted I think... did it as follows. Not ideal but it works!


'#########################################################
'Get the date for the Current User [DAO Connection]
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim MySQL As String

Set db = CurrentDb()
MySQL = "Select * FROM tblUser WHERE tblUser.Username = '" & cuRrentuser & "'"
Set rst = db.OpenRecordset(MySQL)
'#########################################################

Dim DateFrom As Date
Dim SetPeriod As Integer
Dim PeriodWeek As Integer

'set the date to the correct format
DateFrom = Format(rst.Fields("SetDatePeriod&quot, "dd/mm/yy&quot
'set what the weekly period is
SetPeriod = DateDiff("ww", DateFrom, Now, vbMonday, DateFrom)
'always add 1 to the period as the first week we're in is set as 0
PeriodWeek = SetPeriod + 1
'insert the text onto the form
Me.txtCalendarWeek = PeriodWeek

'if the period end (over 8)
If PeriodWeek > 8 Then

'edit the record
rst.Edit
'change the date field for this user
rst.Fields("SetDatePeriod&quot = DateAdd("ww", 8, rst.Fields("SetDatePeriod&quot)
'update this field
rst.Update

'recalculate the weekly period
DateFrom = Format(rst.Fields("SetDatePeriod&quot, "dd/mm/yy&quot
SetPeriod = DateDiff("ww", DateFrom, Now, vbMonday, DateFrom)
PeriodWeek = SetPeriod + 1
Me.txtCalendarWeek = "Week" & " " & PeriodWeek

End If

'closes and resets the DAO recordset
rst.Close
Set rst = Nothing

 

[MichaelRed]

With out going into details, most often, you should just deal w/ the date, not the period in which the date falls. This particularly true for data entry. The average "User" should not be concerned (or even aware) of a period type (even month or week), but just need to get the actual date correct. You (your app) should be responsible for the sorting / grouping stuff.

I have not 'checked' your logic, however in most of the 'roll-over' period processes, the errors / failures occur on the inclusion of a new entity (employee??) within hte period and the issue of wheather the 'roll-over' re-sets' or 'continues' across calendar year boundaries. Again, in a casual scan of your post, I see no reference to either.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over