Trying to calculate sick days

[Gamezy]

Basically I started using access a few days ago, as a computer science student it's been fairly easy and I have a good understanding so far. However, I'm finding it very difficult to calculate data. In particular sick days.

I have a part time database to input that includes the following:

Starting Day
Ending Day
Mon, Tues, Wed, Thurs, Fri (as selection boxes of when the person is working)
Days per week

I need to calculate how many days they are available to work between Start and End. the problem I'm having is that I can't find a way to convert date (as in 24/2/1901) to give the day (Mon, Tues, etc), or a way to record what days a person is working so as to get an accurate measure of the total days they can work.

Example: If someone can work on only Wednesday, but starts work on Thursday, I cannot get the code to take this into account, it will just give me the - total days they can work/days per week they can work.

Unfortunately this is useless as I'm trying to account for the percentage of sick days a person has had, and so needs to more better than approximation.

Any help would be great. I've also been looking into VBA code, but am still unsure as to how I implement it effectively.
EDIT: Have experience with Java and C, so don't be scared to use jargon

 

[Malcy]

Hi
DatePart is your key. One of the parameters gives you the day of the week - I think it is "w" but better check. That returns a number between 1 and 7 where 1 is a Sunday and 7 is a Saturday.
Once you have this it is easy to find out what day of the week a date is.
An alternative is, again I think, "ddd" which should give you Mon, Tue etc.
You might have to do a bit on help, but I think it will help to move you on
Good luck

 

[Gamezy]

Available Days: WorkingDays([Sickness].[Start Date],
IIf([Sickness].[End Date]=Null,Date(),[Sickness].[End Date]))

>>WorkingDays(StartDate As Date, End Date As Date)

This is my query equation thingy at the minute. Where WorkingDays is a custom function (borrowed from another website, credit to the developer whos name I cannot remember). It should find the difference between the Start of the worker and the End Date, but it's currently outputting an #Error into the box whenever the IIf returns Date(). I'm going to use another version of this which looks up a table to check for bank holidays and the such, as to exclude them from the list of available working days.

Any idea's why it's returning an Error when I use Date()? It's probably something simple but I can't seem to figure it out.

PS: Thanks for the quick reply!

 

[workmad3]

Its probably not the Date() part. I'm guessing you are used to java or something as a computer science student (I know I was) but VB and VBA can't check for nulls with [Sickness].[End Date]=Null. You need instead IsNull([Sickness].[End Date]) to see if a value is null or not.

Hope that helps a bit

 

[Gamezy]

Yes, very much used to using Java as a coding language, finding VB very picky by comparison!

Thanks for that!

On another note, I have yet another problem (I know I'm a whiner...wouldn't normally ask, but I am on a bit of a schedule). How can I calculate the total number of Checkboxes ticked?

So far I've been trying to use:

Private Sub Text52_BeforeUpdate(Cancel As Integer)

Dim temp As Integer
temp = M.Value + Tu.Value + W.Value + Th.Value + F.Value
Text52.Text = temp

End Sub

But the text box does not seem to update. Do I need to convert the temp int into a string?

 

[workmad3]

I think you have it in the wrong event. You have it in the before update event of the textbox, which will only fire when an update is about to be performed on the textbox

Instead of putting it there, you need it in the afterupdate event of each checkbox (or write a seperate sub that totals the values, assigns it, and then call this from each of the checkboxes, e.g.:

Private Sub M_AfterUpdate()
    calcCheckBoxes
End Sub

Private Sub Tu_AfterUpdate()
    calcCheckBoxes
End Sub

Private Sub W_AfterUpdate()
    calcCheckBoxes
End Sub

Private Sub Th_AfterUpdate()
    calcCheckBoxes
End Sub

Private Sub F_AfterUpdate()
    calcCheckBoxes
End Sub

Private Sub calcCheckBoxes()
   Text52 = M + Tu + W + Th + F
End Sub

 

[raskew]

Hi -

The following returns the number of days between two dates, excluding those days of the week specified by the user:

Function DateDiffExclude2(pstartdte As Date, _
                         penddte As Date, _
                         pexclude As String) As Integer
'*********************************************
'Purpose:   Return number of days between two
'           dates, excluding weekdays(Sun = 1
'           thru Sat = 7) specified by user
'Coded by:  raskew
'Inputs:    from debug (immediate) window:
'           -excludes Saturday (7) and Sunday (1) from count
'           ? DateDiffExclude2(#2/10/06#, #7/13/06#, "17")
'Output:    110
'*********************************************

Dim WeekHold  As String
Dim WeekKeep  As String
Dim FullWeek  As Integer
Dim OddDays   As Integer
Dim n         As Integer

    WeekHold = "1234567123456"
    'get # of full weeks (7 days) & convert to # of days
    FullWeek = Int((penddte - pstartdte + 1) / 7) * (7 - Len(pexclude))
    'get # of days remaining after FullWeek is determined
    OddDays = (penddte - pstartdte + 1) Mod 7
    'string representation of the weekdays contained in OddDays
    WeekKeep = Mid(WeekHold, WeekDay(pstartdte), OddDays)
    'use boolean statement to reduce OddDays by 1 for each
    'pexclude weekday found in WeekKeep
    For n = 1 To Len(pexclude)
      OddDays = OddDays + (InStr(WeekKeep, Mid(pexclude, n, 1)) > 0)
    Next n
    
    DateDiffExclude2 = FullWeek + OddDays

End Function

HTH - Bob

 

[Gamezy]

Thanks for this guys, you've been a massive help, gonna try and implement this now. Will post again when I (hopefully) I get things working!

 

[Gamezy]

Raskew, I really appreciate your help, but could you please explain that piece of a code a bit more? I'm currently using:

Available Days: DateDiffExclude2([Sickness]![Start Date],IIf(IsNull([Sickness].[End Date])=True,Date(),[Sickness].[End Date]),[«pexclude»])

[«pexclude»] should this input be "1" (for example to skip sunday) and then say "12" to skip sunday and monday?
EDIT: Figured it out...nevermind...

My plan is to use this code to find the ammount of days a person can work, then loop through between the two dates and subtract holiday dates from the final number of workable days. (http://www.mvps.org/access/datetime/date0006.htm I'm gonna attempt to modify this bit of code)

 

[Gamezy]

Ok having a few problems. What I'd like to do is pass in a string into Raskew's existing function. This string would be for example "1457" so that it could find the ammount of days a person is available to work, but checking the values of 5 tickboxes (the ones not ticked, would be added to the string, therefore giving total days someone could work). I've tried all day to find a way of doing this, but I'm just not much good with VB and can't seem to get the checkValue to work, and am having problems using the String Concat. I think it's just my lack of experience. Could someone give me an example of how to do this?

 

[raskew]

Hi -

Use 7 Option Buttons named Opt1 (For Sunday) thru Opt7 (For Saturday).
Label them 'Sun', 'Mon', 'Tue'...etc.

In the after-update event of each button, type: =ProcExclusions()

Copy/paste the following in your form's module:

Private Function ProcExclusions() As String
Dim i           As Integer
Dim TextControl As String
Dim txtHold     As String

   For i = 1 To 7
      TextControl = "Opt" & Format(i)  'create name of control
      If Me(TextControl)  Then  ' = True
         txtHold = txtHold & Format(i)
      End If
   Next i
'   txtExclude is an unbound form field that displays the days,
'   e.g., "17" to be excluded
   Me!txtExclude = txtHold

End Function

HTH - Bob