Results 1 to 15 of 15
  1. #1
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48

    DATEADD & DATEDIFF - SQL Code Provided. Need to call Module - Please Help!! Novice User

    Hi There



    This is my Query:
    =============


    SELECT
    PO.PODate, IIf(PO.[PODate] Is Not Null,DATEADD("w",5,PO.PODate),0) AS SSDate,
    IIf(SSDate Is Not Null,DATEADD("w",1,SSDate),0) AS PSCompDate, ActualDates.SSAD,


    Switch(
    ActualDates.SSAD IS NOT NULL, "Completed", PO.PODate IS NULL AND ActualDates.SSAD IS NULL ,"Not Scheduled", DATEDIFF("w",PO.PODate,Date()) >= 0 AND ActualDates.SSAD IS NULL, "In Progress",
    DATEDIFF("w",PO.PODate,Date()) < 0 AND ActualDates.SSAD IS NULL, "Delayed" ) AS SSuStatus,


    I initially thought that by simply using the "w" with DATEDIFF & DATEADD that it would exclude the weekends. Im not too concerned about public holidays. (Although from all the Vb code I've seen I know I can create a table called tblHoliday and reference it in the module)

    I have created a Module called Workingday and tried copying a 10 different types of code that I found on the forum and tried to get it to work. Simply put : I CANT


    Could someone PLEASE post the module code for DATEADD & DATEDIFF (or is it the same ) using my column names PLEASE... and also edit my SQL above to show how to call the module. If you would be so kind as well to maybe include the holiday part well - I can impress my boss!!

    I'm a total novice and I'm trying to work this out

    Regards

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use DATEDIFF("d",PO.PODate,Date())
    to get the days, qsWorkDays

    make union query,qnNonWorkDays, to pull days to remove:
    the tHolidays table union with qsWeekends (to count the # weekend days.)


    subtract qsWorkDays from qnNonWorkDays

  3. #3
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Im Sorry - but I dont understand what you mean?

    Where do I put this in my code?

    lets leave the holidays for now.

    I think I understand how a union query works

    How do I know whats days to pull to remove and how do I do this?

    I am so weak at access (grrrrrrr)

    Please help.

  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
    If you've tried 10 different methods from this forum, perhaps the problem is in how you're trying to use it. Why don't you post the code you thought most applicable, exactly how you're using it, and the problem you're having (error message, wrong result...).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Explain in text not code what you are trying to do. Give an example of the data in your tables and maybe table names and fields.

  6. #6
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    ===========
    This is The SQL Code in My Query - data comes from two tables : PO & Actualdates
    ===========


    SELECT PO.PODate,
    IIf(PO.[PODate] Is Not Null,DateAdd("w",5,PO.PODate),0) AS SurveyStartDate,
    IIf(SurveyStartDate Is Not Null,DateAdd("w",1,SurveyStartDate),0) AS PlanSurCompDate, ActualDates.SiteSurveyAD,


    Switch(
    ActualDates.SiteSurveyAD Is Not Null,"Completed",PO.PODate Is Null And ActualDates.SiteSurveyAD Is Null,"Not Scheduled",
    DateDiff("w",PO.PODate,Date())>=0 And ActualDates.SiteSurveyAD Is Null,"In Progress",
    DateDiff("w",PO.PODate,Date())<0 And ActualDates.SiteSurveyAD Is Null,"Delayed") AS SiteSurveyStatus,




    IIf(SurveyStartDate Is Not Null,SurveyStartDate+1,0) AS TSSRStartDate,
    IIf(TSSRStartDate Is Not Null,DateAdd("w",0,TSSRStartDate),0) AS PlanTSSRCompDate, ActualDates.TSSRRxAD,


    Switch(
    ActualDates.TSSRRxAD Is Not Null,"Completed",SurveyStartDate Is Null And ActualDates.TSSRRxAD Is Not Null,"Not Scheduled",
    DateDiff("w",SurveyStartDate,Date())>=0 And ActualDates.TSSRRxAD Is Null,"In Progress",
    DateDiff("w",SurveyStartDate,Date())<0 And ActualDates.TSSRRxAD Is Null,"Delayed") AS TSSRRxStatus




    FROM PO INNER JOIN ActualDates ON PO.ID = ActualDates.POID;



    ===========
    I need the DATEADD & DATEDIFF to exclude weekends & public holidays. Currently as you can see Im using "w" .
    I will create a table called Holidays with a date field called PubHolDate - so that I can also exclude public holidays.

    I need help with 3 things:

    1. The code to put into a module
    2. How to reference/call the module in my SQL above
    3. IIf(SurveyStartDate Is Not Null,SurveyStartDate+1,0) AS TSSRStartDate, == [Is this statement correct??] meaning In this statement if (SurveyStartDate +1) falls on a weekend, can we get it to roll over to the next business day. i.e if +1 lands on a Saturday can we get it roll over to the Monday

    Also what will happen if a public holiday falls on a weekend?

    Please let me know if you require any further information.

    Thanking You in advance.

    From an Access Newbie

    ===========
    ===========

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I was looking for the module code and how you call it. In the query, typically:

    FunctionName(FirstDate, SecondDate) As Whatever
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So you are linking PO to ActualDates by the POID to get your data which is working ok? so you get something like:

    PO, ActualDate, SurveyStartDate
    1, 1/1/2017, 1/1/2017
    1, 1/2/2017, 1/4/2017 - if 1/2/2017 is a Saturday?
    1, 2/1/2017, 2/3/2017 - if 2/1/2017 is a holiday?
    2, 2/3/2017
    2, 3/5/2017


    And what you want is if an actual date is on a weekend you want it to go to the next Monday or if any day is a holiday, you want it to go to the next day?

  9. #9
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    HI

    My query is working fine. No issues there.

    My issue is this:

    Example. (dates are dd/mm/yyyy)
    PO is issued on 02/11/2017 (Thursday). So Surveystartdate is currently PODate + 5 days which is (Friday + Saturday+ Sunday +Monday + Tuesday) - This is incorrect. It must be (Friday + Monday + Tuesday + Wednesday + Thursday). Currently it is returning 07/11/2017. It should be 09/11/2017

    I also want to exclude public holidays. So example, in my holiday table if 09/11/2017 was a public holiday then the result would then be 10/11/2017.

    Also, If PODate is 03/11/2017 and we count + 5 days EXCLUDING weekends then TSSRStartDate is Friday 10/11/2017 + 1. That is 11/11/2017. Now that is a Saturday and we need to exclude it. So TSSRStartDate must actually be Monday 13/11/2017.

    I sincerely hope this makes sense. I know nothing of vba/vbscript and I copied and pasted code from the forums trying to match my column names etc to get it to work. It keeps coming up with enter a parameter value. I have no idea what that means.

    I assume that whatever VBA module we create it will work for DATEADD and DATEDIFF. Im posting from my tablet. As soon as Im at my PC I will load the code that I have been trying to use.

    THANK YOU )

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  11. #11
    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 parameter prompt is Access telling you it can't find something (whatever is named in the prompt). Usually you've either misspelled something or forgotten to replace a table/field name with yours.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48

    Please Help - WorkDays

    HI

    Thank You for all the replies.

    I have attached my DB. In the database is the module for working out workdays.


    I have taken it from the link provided earlier (MSDN)

    Could you PLEASE have a look at my query vDateFlow and the associated date fields and how do I incorporate my fields into the VBA. I need to it work for all DateDiff's & DateAdd's
    Also, how do I call that module in my SQL/Query?

    I would also really appreciate any advice on what already has been done - can it be improved/is there a more efficient way of doing it?



    TestWorkdays.zip

  13. #13
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So not sure exactly how you have it but I would loop though each day starting with PODate and check to see if a holiday or Weekend and if so, add 1 to your counter. Maybe something like:

    vPOStartDate = Me.PODate
    vCnt = 1
    For i = vCnt to 5
    If Weekday(vPOStartDate) = 1 or Weekday(vPOStartDate) = 7 or Not Isnull(DLookup("[HolidayDate]", "tblHoliday", "[HolidayDate] = # & Me.vPOStartDate & #") then
    vCnt = vCnt '(if the date is a holiday or weekend, then the counter stays the same)
    Else
    vCnt = vCnt + 1 '(if the date is not a holiday or weekend, then add 1 to counter)
    End If
    vPOStartDate = DateAdd(day,1,vPOStartDate) '(You are adding 1 day each time until your vCnt reaches 5)
    Next i

    Me.POStartDate = DateAdd(d, vCnt, PODate) '(if you had a Weekend in the 5 day span, then vCnt would be 7)


    Not sure syntax is 100% on the Dlookup part.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Two things wrong in your code that I see right off. First, the module and function have the same names, so you'd get an error trying to call the function. Second, the function calls another function, "Weekdays", which is not included in your sample.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Bulzie View Post
    Not sure syntax is 100% on the Dlookup part.
    It's not; you missed the quotes around the value.
    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: 31
    Last Post: 11-08-2017, 11:50 AM
  2. Replies: 9
    Last Post: 09-29-2017, 02:04 PM
  3. Call query with parameters provided
    By hufflehuffle in forum Queries
    Replies: 2
    Last Post: 07-12-2014, 02:01 PM
  4. How to call module using commandline
    By Ranjiz in forum Modules
    Replies: 1
    Last Post: 06-20-2012, 07:50 AM
  5. Change Between by DateDiff or DateAdd.
    By wagner.bts in forum Queries
    Replies: 1
    Last Post: 06-05-2009, 11:24 AM

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