Results 1 to 11 of 11
  1. #1
    Maxland is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    6

    Query based on working days


    Hi, I have a question for my criteria in the query in which I would like to consider today -1 the first business day I tried to apply

    Code:
    Between Date() -"1" And Date()
    But now I have taken a day, e.g. Monday and Sunday and I would like Monday and Friday

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Maybe you can get some ideas from here: https://www.access-programmers.co.uk...-access.37245/

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Most often the two dates used will vary. If that's the case in your app then you could add a reference to a textbox on an open form for each date. Alternatively, you could make it a parameterized query that would require the user to input the dates when it runs.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Maxland,

    More info needed for clarity. Do you have to deal/account for holidays? Further to Bob's comment, could you give us a brief description of the issue and an example or 2?

  5. #5
    Maxland is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    6
    Ok, more about my example, in the table I have tasks completed and in the Date column there is the value of the implementation date, I would like to use a query to make a report of the tasks performed on the previous day, but I noticed that on Monday, instead of taking Friday, the query takes me Sunday.
    I have a question, is there any way to exclude Saturday and Sunday in the same question?
    Code:
    Between Date() -"1" And Date()

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    And what if Friday was a holiday? Now you want to go back to Thursday when running this on Monday?
    What if Monday was a holiday and you run this on Tuesday? Go back one day as you seem to be asking, or go back 2 working days to Friday?
    Or is your situation as simple as you made it out to be? This is a commonly asked question and if you google ms access exclude weekends and holidays you'll get millions of hits.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Maxland,

    You may get some hints from this link. But the question of holidays remains.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Easiest way is have a table e.g. tblCalendary, with a field e.g. CalDate as PK, and a field e.g. DayType with values like 1 - workday, 2 - weekend, 3 - holiday ( you can have other columns there for different needs also). You fill this table for any amount of years you feel reasonable. Now to get latest workday any given number of days before some date e.g. using a query you need a query like
    Code:
    SELECT TOP 1 CalDate FROM tblCalendary WHERE CalDate < (YourDate - DaysBefore) AND DayType = 1 ORDER BY CalDate DESC
    You can use this approach as part of query, or you can create an UDF based on this query, or you can compose a formula based on same logic using functions like DMax().

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have to deal with holidays? This is important to any solution.
    You can use the Weekday function within your logic to identify the day of the week of any Date.
    And, depending on what weekday today is, you can determine which day should represent "yesterday for your requirement". But, if "your yesterday" is a holiday, then you must go back a day and test again.
    Good luck.

  10. #10
    Maxland is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    6
    I tried to apply
    Code:
    SELECT TOP 1 CalDate FROM tblCalendary WHERE CalDate < (YourDate - DaysBefore) AND DayType = 1 ORDER BY CalDate DESC
    but something is wrong. File in attachment
    Database.zip

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    You had to use some real date/number for YourDate and DaysBefore, e.g. declaring them as variables, and initializing them, or using directly values like I did in attached example.

    Remarks:
    Use numeric values instead texts for any type fields - your queries will run faster;
    No need for autonumber key in tblCalendary - every date there must be unique and as follow, CalDate field as PK will be OK.
    Attached Files Attached Files

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

Similar Threads

  1. working days
    By SunTop in forum Programming
    Replies: 3
    Last Post: 04-12-2017, 12:24 PM
  2. How to Add 2, 5 or 30 working days to a date
    By hazeleyre23 in forum Access
    Replies: 8
    Last Post: 06-30-2016, 04:14 AM
  3. Replies: 9
    Last Post: 03-07-2016, 04:17 PM
  4. Replies: 1
    Last Post: 11-26-2013, 09:25 AM
  5. Run Query based on date for Multiple days
    By jedwards85 in forum Access
    Replies: 3
    Last Post: 03-29-2011, 12:04 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