Results 1 to 10 of 10
  1. #1
    Holli is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    46

    Adding a Holiday Table

    I am currently making updates to an existing database and have been tasked with creating a holiday table. I have created the table and added the field name and dates. What is the best approach(where) to add my table to the existing code?



    The currently code reads
    Code:
    Function DateDiffW(BegDate, EndDate)
    Const SUNDAY = 1
    Const SATURDAY = 7
    Dim NumWeeks As Integer
     
    If BegDate > EndDate Then
    DateDiffW = 0
    Else
    Select Case Weekday(BegDate)
    Case SUNDAY: BegDate = BegDate + 1
    Case SATURDAY: BegDate = BegDate + 2
    End Select
    Select Case Weekday(EndDate)
    Case SUNDAY: EndDate = EndDate - 2
    Case SATURDAY: EndDate = EndDate - 1
    End Select
    NumWeeks = DateDiff("ww", BegDate, EndDate)
    DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
     
    End If
    End Function
    Thanks

    Holli

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    One way would be to use a DCount() to count the holidays, using your dates in the criteria. That would give you a number of days you can subtract from the value you're currently returning.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Holli is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    46
    Thanks for your response PBaldy. This is new for me.

    I have a table named Tbl_holiday , Field 1 Description(text), Field 2 HoliDate: 5/25/2015, 7/03/2015, 9/07/2015, 11/26/2016, 12/25/2015

    I looked up DCOUNT(expression, domain, [criteria]) Not sure how to write it or where to place in the code.

    Thanks

    Holli

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    DCount("*", "Tbl_holiday", "HoliDate Between #" & BegDate & "# And #" & EndDate & "#")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Holli is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    46
    Hey Paul,

    Thanks for responding. Yesterday when you wrote

    "One way would be to use a DCount() to count the holidays, using your dates in the criteria. That would give you a number of days you can subtract from the value you're currently returning. "

    What exactly did you mean?

    Thanks

    Holli

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Correct me if I'm wrong, you currently have a function that returns the number of weekdays between 2 dates. I'm suggesting using a DCount() to count the holidays between those same 2 dates, and to subtract that result from the value you're currently returning.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Holli is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    46
    Thanks Paul,

    Don't sure if I am reading the code correctly but I think the BegDate is Monday and The EndDate is Friday.

    Thanks

    Holli

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The function is written to take any 2 dates, and within the function it appears to adjust them to Monday or Friday.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Holli is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    46
    Yes.

    Looking at the code the Sunday = 1 and Saturday = 7 then follow a series of Message boxes if the user selects a Saturday or Sunday from a Calendar.


    Code:
    If Weekday([ADQ2_Complete_Date]) = 1 Or Weekday([ADQ2_Complete_Date]) = 7 Then
        Messagebody = Messagebody & "ADQ Complete Date cannot be on a Saturday or Sunday" & Chr(13)
        i = i + 1
    End If
    Is it possible that I could added another Const called Holiday with a value and then add DLookup with my dates in the criteria for the Holiday table? Is that a could direction to go in?

    Thanks

    Holli

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That looks like it's from someplace else; the function doesn't care. I don't know what a Const gains you; the 2 you mention are just for readability. You can certainly put the result of the DCount() I mentioned into a variable and use that variable in the last line, like:

    VariableForHolidayCount = DCount(...)
    DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate) - VariableForHolidayCount
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-16-2015, 10:17 PM
  2. Weekend/Holiday Exclusion:
    By DHook in forum Modules
    Replies: 5
    Last Post: 12-10-2014, 12:52 PM
  3. how do I not record date of the weekend or holiday?
    By fabiobarreto10 in forum Forms
    Replies: 4
    Last Post: 06-12-2012, 09:33 AM
  4. Payslip Holiday Pay design
    By crxftw in forum Access
    Replies: 5
    Last Post: 07-22-2011, 11:58 AM
  5. function for national holiday
    By barkarlo in forum Access
    Replies: 0
    Last Post: 12-18-2006, 03:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums