Results 1 to 9 of 9
  1. #1
    LindaRuble is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    63

    Get date (minus one day), excluding weekends & holidays in SQL Query to use in Access

    Hello,
    I am trying to limit the results of a SQL query by date. I would like to take the current date from the system
    clock, move back one day, check to make sure it's not a weekend or holiday, then use the result date to limit
    the results in my Query. If the current date minus one day happens to land on a weekend, the date picked
    should be the Friday before the weekend. If the current date minus one day lands on a holiday, it should be
    the date before the holiday as long as it is not a Saturday or Sunday (on another holiday) .

    Here is my SQL query right now.

    SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed
    FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
    WHERE (((RTIClientTracker.Division_Region)='RTI') AND ((RTIClientTracker.Cut)>=Date()-1))
    ORDER BY (Mid(ClientDiv.Client_Division,1,3));

    It limits what it selects by using the current Date minus one day.

    I need to skip over Saturdays, Sundays and Holidays.




    Thank you in advance.


    Linda

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Maybe need a table of holidays. This will have to be maintained as some holiday dates shift each year.

    Will probably want to build a function that can be called in the query that will test if the date is a weekend or holiday.

    Google: calculate weekend and holiday

    Review
    http://www.cpearson.com/excel/holidays.htm
    https://www.accessforums.net/reports...ate-27332.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Agree with june7, you will need a Holiday table and as she said you'll need to keep it current.

    see this discussion for info on Working Days and Holidays
    http://www.utteraccess.com/forum/lof.../t1959936.html

  4. #4
    LindaRuble is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    63
    Hello,

    I looked at everyone's suggestions and Googled some information of my own as well. I have written a function that calls a table "Holidays" and a column "Holidate" with dates in it of the holidays. I want the function to read the system clock and then go back one day. If "today minus one" lands on a Sunday, I want to get the date of the Friday before the weekend. If "today minus one" lands on a Saturday, I want to get the date of the Friday before the weekend. If "today minus one" lands on a Monday that is a Holiday, I want to get the date of the Friday before the Holiday and weekend.

    I would really appreciate it if someone could look at the function below and suggest corrections as it does not work.
    Here it is:


    Function DayBefore() As Date
    CutDate As Date
    If Weekday(Date - 1) = 1 Then 'Sunday
    CutDate = Date - 3
    ElseIf Weekday(Date - 1) = 7 Then 'Saturday
    CutDate = Date - 2
    ElseIf Weekday(Date - 1) = 2 And _
    Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & Date - 1 & "#")) Then 'Monday
    CutDate = Date - 4
    End If
    End Function


    I want to be able to use it in my Query to limit the selection of records with a date.
    Currently I am using Date()-1 but I want to replace it with the function I am writing.
    Here is what my Query looks like now.


    SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed
    FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
    WHERE (((RTIClientTracker.Division_Region)='RTI') AND ((RTIClientTracker.Cut)>=Date()-1))
    ORDER BY (Mid(ClientDiv.Client_Division,1,3));


    Thank you so much for the help. I look forward to your reply.


    Linda

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why doesn't it work - error message, wrong results, nothing?

    If you want a value returned to the procedure (or query or textbox) that calls the function, need last line of function like:

    DayBefore = CutDate
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    LindaRuble is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    63
    Quote Originally Posted by June7 View Post
    Why doesn't it work - error message, wrong results, nothing?

    If you want a value returned to the procedure (or query or textbox) that calls the function, need last line of function like:

    DayBefore = CutDate


    Hello,

    OK, I have added the line that you have suggested IN RED and I still get the following error:

    Compile Error:
    Statement invalid outside Type block


    Function DayBefore() As Date
    CutDate As Date
    DayBefore As Date
    If Weekday(Date - 1) = 1 Then 'Sunday
    CutDate = Date - 3
    ElseIf Weekday(Date - 1) = 7 Then 'Saturday
    CutDate = Date - 2
    ElseIf Weekday(Date - 1) = 2 And _
    Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & Date - 1 & "#")) Then 'Monday
    CutDate = Date - 4
    DayBefore = CutDate
    End If
    End Function


    I test this function by typing the following in the Immediate Dialog Box and hitting ENTER:

    ?DayBefore()


    Any help is appreciated

    Thanks
    Linda

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Need Dim keyword when declaring variables:
    Dim CutDate As Date

    Because it is declared in the function declaration, remove line:
    DayBefore As Date

    Run: Debug>Compile

    Review link at bottom of my post for debug techniques
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    LindaRuble is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    63
    Quote Originally Posted by June7 View Post
    Need Dim keyword when declaring variables:
    Dim CutDate As Date

    Because it is declared in the function declaration, remove line:
    DayBefore As Date

    Run: Debug>Compile

    Review link at bottom of my post for debug techniques
    __________________________________________________ _________________________________________


    Hello,

    I've made changes to the code as you had suggeted but it does not return the value that I want

    Here is the code with the changes:


    Function DayBefore() As Date
    Dim CutDate As Date
    If Weekday(Date - 1) = 1 Then 'Sunday
    CutDate = Format(Date - 3, "mm/dd/yyyy")
    ElseIf Weekday(Date - 1) = 7 Then 'Saturday
    CutDate = Format(Date - 2, "mm/dd/yyyy")
    ElseIf Weekday(Date - 1) = 2 And _
    Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & Date - 1 & "#")) Then 'Monday
    CutDate = Format(Date - 4, "mm/dd/yyyy")
    DayBefore = CutDate
    End If
    End Function


    This is what I enter in the Immediate Window: ?DayBefore()
    This is the result I get: 12:00:00 AM

    What I want is a date. If the current date minus one lands on a weekend or a holiday, I want to get the date of the Friday before the weekend or the day before the holiday.

    Please let me know what I am doing wrong. Any help would be really appreciated.

    Thanks


    Linda

  9. #9
    LindaRuble is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    63
    Hello,

    I figured out the code that I needed. It takes the system clock date and outputs a date depending on if the day before is the weekend or a holiday. Here it is.

    ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++

    Function DayBefore() As Date

    TheDate = Date
    DayBefore = Format(TheDate, "dd/mm/yyyy")
    ' Test for Sunday
    If Weekday(TheDate - 1) = 1 And _
    IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & Date - 1 & "#")) _
    Then
    DayBefore = Date - 3
    ' Test for Saturday
    ElseIf Weekday(TheDate - 1) = 7 And _
    IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & Date - 1 & "#")) _
    Then
    DayBefore = Date - 2
    ' Test for WorkDay
    ElseIf Weekday(TheDate - 1) <> 7 And Weekday(TheDate - 1) <> 1 And _
    IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & Date - 1 & "#")) _
    Then
    DayBefore = Date - 1
    ' Test for Holiday on Monday
    ElseIf Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _
    & TheDate & "#")) And Weekday(TheDate - 1) = 2 Then
    DayBefore = Date - 4
    ' Test for Holiday NOT on Monday
    ElseIf Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _
    & TheDate & "#")) Then
    DayBefore = Date - 1

    End If
    End Function

    ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++

    Thanks

    Linda

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

Similar Threads

  1. Replies: 14
    Last Post: 09-11-2013, 01:10 PM
  2. quarterly, excludes weekends and holidays
    By madagaluna in forum Queries
    Replies: 2
    Last Post: 04-01-2011, 12:56 PM
  3. Replies: 0
    Last Post: 04-01-2011, 09:12 AM
  4. How to analyze holidays/weekends
    By Galio83 in forum Queries
    Replies: 0
    Last Post: 03-31-2011, 12:27 PM
  5. Query DateDiff calculation excluding weekends
    By Masterfinn in forum Queries
    Replies: 3
    Last Post: 04-01-2010, 09:46 AM

Tags for this Thread

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