Results 1 to 15 of 15
  1. #1
    DMT Dave is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    916

    Date Diff Correction

    Hi Guy's, hope you can give me help on this what I should find so simple



    I am trying to use date diff and can't think of the correct calculation, so I have fields called

    DateAdded
    DateDue (auto 21 days after DateAdded)

    I have a variable called
    Days Remaining

    I have tried a few switch around on calculations but all coming back with either -20 or 20

    Target is the the days remaining variable kind of counts down and once 7 or under, the font is red which is 1 week remaining

    The intDays
    And
    The strDays

    Do I calculate Date Added vs DateDue fields ?
    Do I calculate Date Added fields vs Now ?
    Do I calculate Date Due fields vs DateAdded fields ?
    Do I calculate Date Due fields vs Now ?

    Below is the result currently with the last 3 table columns as other data is private

    all good apart from a simple calculation that even I should be able to do!!!!!!!!!!!!!!!

    the bottom date added has been moved back to test so the days remaining should be in red and/or minus figure ?

    Click image for larger version. 

Name:	Capture.JPG 
Views:	20 
Size:	30.1 KB 
ID:	49192

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,521
    Don't you want to know how many days between Now and DateDue?

    Why does the last record not show DateDue 21 days from DateAdded?
    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
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    106
    Try DateDiff("d", Date() , DateDue)
    Groeten,

    Peter

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Posts
    1,255
    Something like this?

    Code:
    datediff("d",DateAdd("d",21,DateAdded),date())
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    3,108
    Why Now() ?
    That just introduces a time element which is NOT required?
    Just use Date().
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    DMT Dave is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    916
    Hi Guy's thank you, i was lost with a simple calculation

    @June7, I manually changed the date so it would go beyond 14 days to test the days remaining field text to go RED

    Thank you all i will adapt what i should have been able to do in the first place

  7. #7
    DMT Dave is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    916
    I meant to add the code but was side tracked:

    Code:
    'SET THE TABLE AND HEADERS    strHTML = "<HTML><Body><table border='3' width='70%'><tr><th>Del To</th><th>Town</th><th>PostCode</th>" & _
            "<th>MFG</th><th>SL</th><th>Qty</th><th>Date Added</th><th>Date Due</th><th>Days Remaining</tr>"
    'LINK BODY WITH TABLE
            strBody = strHTML
    'SET THE RECORDS
        Set rs = CurrentDb.OpenRecordset("Select * From tblStorage Order By DateDue;")
    'START LOOP
        Do While Not rs.EOF
    'SET DATE NOW
    dtNow = Format(Now(), "dd/mm/yyyy")
    'ADD VARIABLE FIELDS
    
    
            intDays = DateDiff("d", dtNow, rs.Fields("DateDue"))
            
            Select Case intDays
            
                Case Is > 7
                
                strDays = "<font color='blue' size='3'>" & DateDiff("d", dtNow, rs.Fields("DateDue")) & "</font>"
                
                Case Else
                
                strDays = "<font color='red' size='3'>" & DateDiff("d", dtNow, rs.Fields("DateDue")) & "</font>"
                
            End Select
            
    'SET THE MAIL BODY
        strBody = strBody & "<tr><td>" & rs.Fields("DelTo") & "</td><td>" & rs.Fields("Town") & "</td><td>" & rs.Fields("PostCode") & "</td><td>" & rs.Fields("MFG") & _
        "</td><td>" & rs.Fields("SL") & "</td><td>" & rs.Fields("Qty") & "</td><td>" & Format(rs.Fields("DateAdded"), "dd/mm/yyyy") & "</td><td>" & _
        Format(rs.Fields("DateDue"), "dd/mm/yyyy") & "</td><td>" & strDays & "</td></tr>" & "|"
    'MOVE THROUGH RECORDS AND LOOP TOGETHER
       rs.MoveNext
       Loop
       
    'SET THE MAIL UP NOW
    
    
        Set olItem = olApp.CreateItem(olMailItem)
        Set olAccount = olApp.Session.Accounts.Item(1)
        With olItem
            .To = strMailTo etc..........

  8. #8
    DMT Dave is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    916
    Sorry guy's and thank you, i needed to move the computer date to within 7 days !!!! durrr silly me, thank you, this works

    Code:
    dtNow = Date
    
    'ADD VARIABLE FIELDS
    
    
     
            intDays = DateDiff("d", dtNow, rs.Fields("DateDue"))
          
            Select Case intDays
            
                Case Is > 7
                
                strDays = "<font color='blue' size='3'>" & DateDiff("d", dtNow, rs.Fields("DateDue")) & "</font>"
                
                Case Else
                
                strDays = "<font color='red' size='4'><B>" & DateDiff("d", dtNow, rs.Fields("DateDue")) & "</font></B>"
                
            End Select
    Click image for larger version. 

Name:	Capture.JPG 
Views:	18 
Size:	30.0 KB 
ID:	49193

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,365
    Am I the only one who thinks this
    so I have fields called

    DateAdded
    DateDue (auto 21 days after DateAdded)
    should not be stored? MAYBE it would make sense if you could use an expression as a default value like =[DateAdded]+21 in a date/time field - but you can't? So the calc is being done some other way? I wouldn't think of storing this no matter how or where it's calculated. Am I missing something? A basic tenet has changed?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    106
    Quote Originally Posted by Micron View Post
    Am I the only one who thinks this
    It crossed my mind. But we don't know enough to be sure. Maybe it must be possible to change the date due later on.
    Groeten,

    Peter

  11. #11
    DMT Dave is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    916
    hi Micron, are you suggesting creating a new field in the table and add that field at the end of the recordset instead of creating a variable within the loop ?

    Is that a more reliable method ?

    I don't need to store the days remaining, I can i guess but my next adjustment is the set the email within the case statement so that once any day remaining value gets down to 7 days, generate a reminder email

  12. #12
    DMT Dave is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    916
    I guess the methods are:

    1: construct a query based SQL and add the days remaining as last field with the calculation as suggested in earlier posts:

    Set the rs to strSQL

    2: add a field in the table
    Set rs to table

    3 set variables within a loop
    set rs to table

    I think @ Micron you refer to adding the field as per (1) ?

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,365
    are you suggesting creating a new field in the table
    No. You've already stated you had a field in a table for the calculated (due date). I don't do that as a rule. I either do it in queries or using calculated controls on a form. Either way you get/calculate the due date I'm saying you normally don't store calculations in tables. See this, for one
    http://allenbrowne.com/casu-14.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    3,108
    Interesting that Allen calls a control event. I was led to believe it was better not to do that, but create code in a module and call that from both events.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  15. #15
    DMT Dave is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    916
    OK thank you, just come back to it now after making apple crumbles from apples off dads tree

    Will look at Allen Browne link

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

Similar Threads

  1. Date Diff In Query
    By DMT Dave in forum Access
    Replies: 4
    Last Post: 09-17-2020, 02:42 PM
  2. Date Diff to calculate hours.
    By rywello in forum Queries
    Replies: 4
    Last Post: 05-30-2018, 02:13 PM
  3. Replies: 3
    Last Post: 03-02-2016, 09:39 AM
  4. Date diff on Forum
    By Auto in forum Forms
    Replies: 5
    Last Post: 11-20-2013, 11:00 PM
  5. Date diff trouble
    By Cran29 in forum Queries
    Replies: 3
    Last Post: 03-11-2011, 01:02 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