help with working days

[philbennison]

I am creating a database, and i have two fields 'date in' and 'date out'.
Is there a simple command to put in the table or form to work out how many working days have elapsed between the two dates. (Similar to 'networkdays' in excel).
I have no expierence with code, macros etc.
do i need to put in the command in the table,form, or query

johnessex said i need to do a search on workingdays, but i dont know where or how to do this. workingdays is not a field so where do i put it ?

Please help

Phil

 

[ecniv]

Search via google or by the search button for the forums (link somewhere on the page).

Working days in excel - not a problem as you've found.
In Access however I don't think there is an option, so its either a chunk of code or an Ms function which wasn't mentioned before (possible datediff - check the helpfile).

 

[M8KWR]

What you would need is this

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer

' Accepts two dates and returns the number of weekdays between
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

if you paste the above code. The next step would be to say something like

If Me.txtdate1 = "" Or IsNull(Me.txtdate1) Or Me.txtdate2 = "" Or IsNull(Me.txtdate2) Then

Else

MsgBox WorkingDays(txtdate1, txtdate2)

End If

You would have to change the name of the text boxes atc. I have jsut put a message box to display, but if you want this to update a field then you would need

txtWorkings = WorkingDays(txtdate1, txtdate2)

I hope this helps, if you require any additional help please ask.

 

[philbennison]

additional help please - MK8WR

Thanks for the code, however i am not very good at it. Is there any chance of some more help.

I have copied the main body of code into visualbasic.
but i am not to sure about the next bit

If Me.txtdate1 = "" Or IsNull(Me.txtdate1) Or Me.txtdate2 = "" Or IsNull(Me.txtdate2) Then

Else

MsgBox WorkingDays(txtdate1, txtdate2)

End If

You would have to change the name of the text boxes atc. I have jsut put a message box to display, but if you want this to update a field then you would need

txtWorkings = WorkingDays(txtdate1, txtdate2)


Where do i put this ??
do i replace 'txtdate1' with 'date in' (which is my field name ??)

Thanks for your help so far

Phil

 

[M8KWR]

sorry my mistake. I should of explaiined further. Paste the following

Private Sub date_in_AfterUpdate()

MsgBox WorkingDays(date_in, date_out)

End Sub

Private Sub date_out_AfterUpdate()

MsgBox WorkingDays(date_in, date_out)

End Sub

What this will do if either date is updated the it will display the number of working days between the two date.

Again this will only bring up a message box displaying the number of days, if you have a working days text box that you need to update then replace the "Msgbox" with for example

working_days = WorkingDays(date_in, date_out)

I hope this help now. But if there is anything else please let me know, i'm glad to help.

 

[philbennison]

MK8WR - sorry - am i dense

Sorry to keep bothering you. i have now entered the main body of txt, and the other two bits you have given me. i have changed my textbox which i want to display the working days to 'txtworkings', and changed the name in the last two procedures. i have also changed my date in to txtdate1 and date out to txtdate2. I have now changed the control source in design form to read 'txtworkings'. however nothing is working ???!

My plan is that after the enduser enters date in and date out (txtdate1 and txtdate2) the working days (txtworkings) will also be updated.
I am sorry to keep asking but i am not very profient with access. Am of home now but any advice you could give me, will be implemented first thing in the morning !!

Thanks

Phil

 

[M8KWR]

So let me get this correct you have the following controls (text boxes)

txtdate1
txtdate2
txtworkings

if this is correct then delete what you have copied and pasted, and copy the following it, and then it should all work (hopefully). If you entered no code before this then delete it all.

I have tested it so it should work.

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer

' Accepts two dates and returns the number of weekdays between
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


Private Sub txtdate1_AfterUpdate()

txtworkings = WorkingDays(txtdate1, txtdate2)

End Sub

Private Sub txtdate2_AfterUpdate()

txtworkings = WorkingDays(txtdate1, txtdate2)

End Sub


If this still does not work please let me know, and if it does work then also please let me know.

 

[philbennison]

MK8WR - what am i doing wrong

sorry bout this, i have deleted all visual basic code. cut and pasted the new code in vbasic. i still is not working.
I have also changed the format of the boxes to text, to see if that worked. but i am still not having any joy
what am i doing wrong?
when you tried it did the number of days update on form as soon as you typed the end date ?
what is the meaning of life ?

please can you help ??


Phil
still confused

 

[M8KWR]

What i have done is attached a form that show you how it works, i have tested it and it does work, so hopefully it will work so you.

All you would have to do is change the labels in your code to show you fields name/values etc

Hope this help and works, if you are still stick please let me know.

 

[philbennison]

MK8WR - thanks

Thanks alot for your help. i have now downloaded your file and am going to transfer all my other fields onto your sheet. When i tried to open it the first time, i said that it would need to be converted as the versions were different. I dont know if this is what caused the problem ??
I also tried to look at your code, but cant seem to find it, so i dont know what has happened to that !!

Thanks ever so much for all you help

I really appreicate it.

Bye .....(for now !)

Phil

 

[snoko]

Phil try this ....

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function