Results 1 to 12 of 12
  1. #1
    StevenCV is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    21

    Question Date Difference Not Quite Right

    Hi,



    I have an unbound text box in a report which calculates how long a client has been on a service, with this control:

    =DateDiff("d",[date_of_referral],[discharge_date])

    However, it does not seem to calculate how I need it to.

    For instance, if the date_of_referral is 28/09/2013 and the discharge_date is 30/09/2013, then the field above shows 2 days, which IS the difference between the two dates.

    However, for our records, the client was on the service for 3 days, on 28th, 28th and 30th.

    Do I need to change DateDiff to something else in order to calculate this correctly?

    I considered making it plus 1 to the datediff, but this wouldn't work when the client was on the service for one day.

  2. #2
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    Datediff as the name suggests, will calculate only the difference between two dates. In your case, if I understand correctly, to count number of records within a date range, you need
    DCount function
    See here for example https://www.accessforums.net/program...nge-38432.html
    Remember to use the "#" delimeter for date fields.

  3. #3
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Do I need to change DateDiff to something else in order to calculate this correctly?

    I considered making it plus 1 to the datediff, but this wouldn't work when the client was on the service for one day.
    That sounds exactly like what you need to do.
    I don't understand why that wouldn't work. Perhaps you can post an example in which it doesn't work.


    EDIT: I just saw amrut's reply. From your original question, I didn't get the impression that you were trying to count records, but if that is correct, then it makes sense why it isn't working and you would want to follow his advice.

  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
    How about:

    DateDiff(...) + IIf(Date1 = Date2, 0, 1)

    though I also wonder why adding 1 doesn't work.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    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
    I think DateDiff is doing what it's suppose to. I think the issue is that you really are asking a slightly different question.
    How many days did we provide service? You provided service on days 28, 29 and 30.

    I think Paul has it. DurationOfService = DateDiff(...) + IIf(Date1 = Date2, 0, 1)

  6. #6
    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 thought I had it but the more I think about it the more I think you just add 1. If the dates are the same, the DateDiff() would return 0, so adding 1 would give you the single day. Now I've confused myself.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I thought I had it but the more I think about it the more I think you just add 1. If the dates are the same, the DateDiff() would return 0, so adding 1 would give you the single day. Now I've confused myself.
    I can't think of a situation in which you would not want to add 1 (which is why I was confused by the question in the first place).
    Unless we are not understanding something, or there are some other factors which have not been revealed to us...
    Maybe amrut's on the right track with this one in that they are looking for a record count. I am kind of confused by what is meant by "on the service". Maybe if that is defined, it will become clearer!

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    why not simply

    datediff("d", [FirstDate], [SecondDate]) + 1

    Even if they are on the service for one day you don't want a returned value of 0 because they actually had a service no?

  9. #9
    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
    Yes, it seems add 1 when you're dealing with duration ---to include the first day.
    and DurationOfService = DateDiff(...) +1 seems to work.

    Until we hear from the OP, we really won't know if it was a count issue or what.

  10. #10
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    why not simply

    datediff("d", [FirstDate], [SecondDate]) + 1
    That is the $64,000 question we have all been asking!

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    OP probably didn't realize that the result will be 0, not 1, if the start and end dates are the same. Always +1 if you want both ends included in the count. For instance, if you want to know how many nights in a hotel reservation (from checkin to checkout), don't +1.
    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.

  12. #12
    StevenCV is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    21
    Hello all, sorry for the delay in getting back to you all. It seems there is some confusion, and I have to say it is my fault.

    When I was testing, didn't realise it would count as 0, I thought it was coming up as 1. That's me not showing enough attention to detail, so I apologise.

    To clear things up though, we care for elderly people. They get referred to us (the start date field), we care for them for any number of days, and then get get discharged (the end date field), and all my dates were out by 1.

    The plus 1 does seem to have worked now, so all I can do is say sorry once again for wasting everyone's time, but thank you all very much for assisting.

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

Similar Threads

  1. Date difference
    By Kaaivin in forum Queries
    Replies: 2
    Last Post: 07-03-2013, 06:06 AM
  2. Replies: 3
    Last Post: 04-24-2013, 10:44 AM
  3. Date Difference from same column
    By akmehsanulhaque in forum Queries
    Replies: 1
    Last Post: 02-19-2013, 01:31 PM
  4. Replies: 1
    Last Post: 02-12-2013, 03:48 AM
  5. Replies: 2
    Last Post: 01-23-2013, 11:07 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