Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working days info (Access 97 Win2k)

    Hello again

    I'm trying to work out the actual working days between any two dates. I've found two useful posts, 187790 and 85675, either one of which looks like it will solve my problem. The only question I have is where do I put the code?

    I'd like to be able to use the result on a form and a report, to allow the user to view the information and then print it out if they want to. Would attaching the code to a buttons 'on click' property along with the code to open a form solve my problem? If so how do I get the result from the code into either a text box or label?

    I think I have a few ideas, but not really sure if what I want to do is possible and at the minute I can't get it to work.

    Thanks for the help.

    Ian

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working days info (Access 97 Win2k)

    Me.txtResult.Value = WeekdaysMinusHolidays(Me.FirstOfMonthDate.Value, Me.LastOfMonthDate.Value)

    I use this on a form load event.
    Presuming you have the workdays function as a stand alone module.
    txtResult is an unbound text on my form.
    WeekdaysMinusHolidays is the module (The declaration text not module saved name.)
    Me.FirstOfMonthDate.Value = date field on same form
    Me.LastOfMonthDate.Value = date field on same form

    Again I have the above two fields already populated.

    Hope this gets you started.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working days info (Access 97 Win2k)

    Create a standard module (activate the Modules tab of the database window and click the New button on the right hand side). Enter or paste the function you want to use there. You will find a simple function that only omits weekend days here and a more complicated function that uses a list of holidays here (both on the Access Web).

    If you prefer, you can copy this function and paste it into a module instead:

    Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
    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 WeekDay(DateCnt) <> 1 And WeekDay(DateCnt) <> 7 Then
    EndDays = EndDays + 1
    End If
    DateCnt = DateAdd("d", 1, DateCnt)
    Loop
    Work_Days = WholeWeeks * 5 + EndDays
    End Function

    This function counts the number of days between two dates excluding Saturdays and Sundays. The ending date isn't counted, so the number of working days between a Monday and the next Monday is 5. If you want to count the ending date, replace Do While DateCnt < EndDate by Do While DateCnt <= EndDate.

    Let's say you have two text boxes on a form, txtDateFrom and txtDateThru, and you want to display the number of working days between them.
    Put an unbound text box on the form and set its Control Source to

    =Work_Days([txtDateFrom],[txtDateThru])

    You can also use the function in a query. Say you have a table with fields DateFrom and DateThru. In a query based on this table, create a calculated field DaysBetween as follows:

    DaysBetween: Work_Days([DateFrom],[DateThru])

  4. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working days info (Access 97 Win2k)

    Hans

    As usual you are a real star, the ability to put this into a query was where I'd have been going next. I'd found the posts on the Access site by following the link to the 'simple' one that is on this forum.

    Dave

    Thanks, the information answers the one question I had left after Hans' answer (I can run the 'independant' code from a form event).

    This place is keeping me sane <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Thanks again

    Ian

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working days info (Access 97 Win2k)

    Hope these answers solve your quest.

    About the sane bit, I'm not sure.
    I've been going insane for quite a while now.
    The more I learn, the more possibilities I see, the more insane I go.

    I think Hans will back me up on that.

    Have fun.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working days info (Access 97 Win2k)

    Dave, why ask me to state the obvious? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Don't worry, be happy <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working days info (Access 97 Win2k)

    Because you've pulled me from the brink as I've been dangling on the edge so many times. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  8. #8
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working days info (Access 97 Win2k)

    Hans / Dave

    Thanks, I'm 'back from the brink' as it were, tried the code and had a problem with the sample posted by Hans, it did not like the '&lt' bit at all (Access 97??), but once I'd checked out the other samples that were available from the links I figured out a way to make the thing work, but not why it wouldn't, what does the '&lt' do in the code, I can't really find any refernce to it in the material I have, other than & as a way of setting values (no suggestion as to what value 'lt' is if that's the answer).

    I now have a sample query and a sample form that will produce the number of working days between datein and dateout. I'm ignoring the holiday bit as the database is for our Sales, and international customers don't all have the same public holidays as the UK. If I start to get 'over confident' I'll replace the current code with the set that allows for the use of a holiday table and put Christmas and Boxing Day in there....

    As the Do ... Loop is very poorly covered in 'Access 97 programming for dummies', what book should I get next? (Dummies is about my current level of ability though)

    I'm off to celebrate with a stiff coffee, thanks again.

    Ian

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working days info (Access 97 Win2k)

    Hi Ian,

    The problem you mention has bothered others too; it is not in the code itself but in the browser. In my code, I had a "less than" symbol. Sometimes, the browser will display the HTML code for "less than" instead of the symbol itself. This code is & lt (without a space in between). If you replace this by the "less than" symbol, the code should work.

    BTW, Do...Loop is explained in the online help for Access 97; you'll find several examples there (look up Do...Loop or Loops in the help index).

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working days info (Access 97 Win2k)

    Here's my module for Holiday-WorkDays and the table for holidays, (if you want them).

    I've converted to A97.

    The other problem you'll have to check the attachment.
    This seems to be a browser problem.

    If I'm on the right trak, change them as with attached.
    The text in the Attached is how my browser see' it in Outlook Express.
    Don't know why it happens, but it does.
    Attached Files Attached Files

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working days info (Access 97 Win2k)

    Sorry heres the module
    Attached Files Attached Files

  12. #12
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working days info (Access 97 Win2k)

    Hans

    Once I'd taken the time to tully understand what was happening in the code I replaced the '<' with a less than sign, presto it worked. In the long term it's better than just copying the code and only partially understanding what it does.

    The Access on line help, is this on the Microsoft site? or are you referring to the help file off the menu in Access. If the latter, then I've read this and it makes great sense if you want to do something very simple, maybe I'm looking at the wrong part of the problem with my views of Do .... Loops and the Dummies book, my problem may be with what can be done while inside the actual loop, think I need to get a function reference from somewhere..... ://ambles off with steam coming out of ears//:

    Dave

    Thanks for the module, I'll have a look through it, but the code from the other Access site helped me figure out what was happening and once I'd corrected a few typos (I typed it in myself in order to try and get the methodology to sink in) it ran fine the way you told me to set it up.

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •