Results 1 to 10 of 10
  1. #1
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281

    Subtracting dates

    Hello ~



    I am trying to setup a button to produce an immediate week-ending report.

    In the textbox the Sup will enter (typically) a Saturday date and then click a button to produce a report that will begin on the previous Sunday and run through the date entered into the textbox

    I am currently trying to get something like the following to work:

    Code:
    Between ([Forms]![frmSalesReports]![TxtWkEnd]-6) And [Forms]![frmSalesReports]![TxtWkEnd]
    As always ~ Thank you for your help

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is an older answer to question

    Code:
    What is the date of the previous Sunday?
    
    y = dateadd("d", - weekday(x) +1, x)
    
    where x is any date and y is the Previous Sunday
    
    (by raskew)
    Question for you--- What if the Sup enters a Friday or Wednesday?

  3. #3
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey Orange ~Sorry for the late response.

    So, the concept behind creating this event is simply to provide Sups with essentially a 'one-click' report.

    Ultimately, they can put any date in the textbox and it will provide a report from that date going back 6 days.

    However, each Monday they are asked to populate two textboxes to run the exact same report...from the previous Saturday through the previous Sunday.

    Also, once I get this date thing figured out I will then put an if IsNull condition on the textbox and if that condition is true then to run the current previous week report then would merely have to click the button and be done!

    I hope that provides a little more color to what I am trying to create.

    That said, my research has brought me to the below code, which, while it doesn't error out, it also doesn't provide any results...So I guess I'm still trying to figure this out..

    Code:
    Between DateAdd("d",-6,([Forms]![frmSalesReports]![TxtWkEnd])) And ([Forms]![frmSalesReports]![TxtWkEnd])
    Thanks as always for any help...

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The sample code I showed will accept any Date (x).
    It then "automatically" identifies "Y" the last Sunday prior to Date(x), or if Date(x) is a Sunday, uses that.

    So you could use "Y" as the report startDate and y+(whatever) as the report end date.
    The Sup would only have to Enter or select 1 date.

    I'm not sure I follow why user has to enter a date, and then on Monday enter data again to run the same report.

  5. #5
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Thanks for sticking with this Orange...

    I'll work with your idea to see if I can get it to work with textboxes.

    As for the reporting schedule... since we are closed on Sunday the first opportunity Sups have to run this report for the previous week is when they return on Monday.

    Thus, on Monday 10/23/2017 they will run the report for 10/15/2017 through 10/21/2017 (even though we are closed on Sunday there is a 1/500 chance that someday the employees will be asked to work a Sunday...so I include it in the date range as a just in case)

    Hope that makes sense...

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just a thought - if on a Monday they always get a report for the week previous (Sun through Sat).
    Then could you have a set up,(using your example) Sup opens a form on Oct 23 to get the report - and behind the scene you calculate the
    Sunday (Oct 15) and supply report parameters Startdate Oct15, Enddate Oct 21.

    I realize it is a change in your procedures, but it may eliminate typos and streamline things. Just a thought for consideration.

  7. #7
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    I see where your going with your idea Orange...

    The button that will eventually provide a one-click report is part of a larger 'Reports Form' that, if, lets say the Sup is on vacation may not be opened up on Monday.

    I'm trying to get your idea to work within the structure below:
    Code:
    Between DateAdd("d",-6,[Forms]![frmSalesReports]![TxtWkEnd]) And [Forms]![frmSalesReports]![TxtWkEnd]
    Any ideas?

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's a demo procedure. I added 6 days to today (Date())
    to work with your sample data

    Code:
    Sub getSunday()
    10  Dim x As Date: x = Date + 6
        Dim y As Date
    20  y = DateAdd("d", -WeekDay(x) + 1, x)
    30  Debug.Print "The most recent Sunday was " & y & vbCrLf & "So you could use " & y - 7 & " thru " & y - 1
    40  MsgBox "The most recent Sunday was " & y & vbCrLf & "So you could use " & y - 7 & " thru " & y - 1
    End Sub
    Which gives

    The most recent Sunday was 22-Oct-2017
    So you could use 15-Oct-2017 thru 21-Oct-2017



    You could make the procedure into a function, or you could adapt it to your report selection code.

  9. #9
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey Orange ~ Just wanted to say thanks for all your help...

    Being new at this sucks... I am unable to get your idea to work in query design mode in the Criteria field

    On another note... When I enter a date into textbox: TxtWkEnd then plug DateAdd("d",-6,[Forms]![frmSalesReports] into the immediate window it produces the correct result

    So I know the code is being correctly interpreted but it is not bringing back any results; and yes, there is data in the table.

    I must admit, I didn't think Access was this difficult...

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It was just a suggestion. And a question re a possible change to your process.
    Does the Sup have to enter anything? If so, why? It's just getting the requirements understood.
    You can make the startdate calculation into a function (adjust what I have shown).
    Do you ever have to get a report for a different week?

    Here's a function with some test materials ( may not mean much to you yet)

    The function

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : getSunday
    ' Author    : mellon
    ' Date      : 17-Oct-2017
    ' Purpose   : To determine the most recent Sunday from a given date, or from today if no Date is supplied.
    ' Then, return the date of the Sunday one week earlier.
    '
    ' If no Date is supplied, today's Date will be used.
    ' If a Date is supplied, that Date will be  used.
    ' If a non-Date (alpha etc) is supplied, an error message will display and error number 5656 will be returned
    '---------------------------------------------------------------------------------------
    '
    Function getSunday(Optional DateX As Variant) As Date
    10    On Error GoTo getSunday_Error
    
    20    If IsMissing(DateX) Then DateX = Date
    30    If Not IsDate(DateX) Then
    40      MsgBox "Invalid parameter passed---Error!! " & vbCrLf _
                   & "You must supply a real date or nothing !", vbCritical
    50      getSunday = #1/1/1000#
    60      err.Raise 5656
    70      Exit Function
    80    End If
        Dim y As Date  'the most recent Sunday
    90    y = DateAdd("d", -WeekDay(DateX) + 1, DateX)
    100   getSunday = y - 7    'to get the start of the week previous to the most recent Sunday
    110
    
    getSunday_Exit:
    120   Exit Function
    
    getSunday_Error:
    130   MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure getSunday of Module ModuleTesting_CanKill"
    140   Resume getSunday_Exit
    End Function
    The test routine ( I manually adjusted the calling routine)
    Code:
    Sub testgs()
    Dim i As Integer
    Dim hold As Variant
        
              'test1 = #9/28/2017#  getSunday(#9/28/2017#)
              'test2 = "xyz"        getSunday("xyz"
              'test3 =  no parameter getSunday()
    10    Debug.Print "**  getSunday() "
    20    hold = getSunday()
    30    If hold = #1/1/1000# Then
    40      MsgBox "A non date parameter was entered --Error!!!"
    50      Exit Sub
    60    Else
    70      Debug.Print "The most recent Sunday was " & hold + 7 & vbCrLf & "So you could use " & hold & " thru " & hold + 6
    80      MsgBox "The most recent Sunday was " & hold + 7 & vbCrLf & "So you could use " & hold & " thru " & hold + 6
    90    End If
    100
    End Sub
    Outputs: based on the function call setup

    ** getSunday(#9/28/2017#) '<<<<<<<<<This let's you choose a different week
    The most recent Sunday was 24-Sep-2017
    So you could use 17-Sep-2017 thru 23-Sep-2017

    ** getSunday('xyz')
    ***Error msgbox displayed

    ** getSunday() '<<<<<<<<<<<<this is the default
    The most recent Sunday was 15-Oct-2017
    So you could use 08-Oct-2017 thru 14-Oct-2017

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

Similar Threads

  1. Replies: 2
    Last Post: 09-19-2017, 11:31 AM
  2. Replies: 2
    Last Post: 03-01-2016, 04:48 AM
  3. #ERROR when subtracting dates
    By nlkehl in forum Queries
    Replies: 7
    Last Post: 08-26-2014, 08:24 AM
  4. Adding and subtracting dates from current date
    By brianmcleer in forum Access
    Replies: 1
    Last Post: 05-07-2013, 05:15 PM
  5. Subtracting Dates in Queries
    By seanpbent in forum Access
    Replies: 1
    Last Post: 02-01-2013, 04:28 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