Results 1 to 7 of 7
  1. #1
    Pilot is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    3

    DateDiff not returning correct results

    As part of a project, I need to Call the difference between two dates (and some other information - not included in this code), passing a date value and using the current system date.
    Because we use UK date system, I convert the dates to US format using another function. The problem is that the system date converts to US format, but not the date passed to the function.
    The two functions are:
    Public Function GetDiff(xDate As Date) As Integer
    Dim yDate As Date
    Dim zDate As Date
    Dim dateD As Integer

    zDate = makeUSDate(xDate) 'Date passed to function as #15/01/2020# - doesn't convert to US DAte
    yDate = makeUSDate(Date) 'Todays date 11/01/2020 - sucessfully converts to US Date
    dateD = DateDiff("d", xDate, yDate)
    GetDiff = dateD
    End Function
    ___________________
    Function makeUSDate(x As Date)
    If Not IsDate(x) Then Exit Function
    makeUSDate = Month(x) & "/" & Day(x) & "/" & Year(x)
    End Function



    Result from the intermediate window:
    ? getdiff(#15/01/2020#)
    291


    I can't figure out why one date converts and the other doesn't

  2. #2
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    you could just use the format function.
    Format(X, "mm/dd/yyyy")

    and if you need to make a date use the serial function
    https://support.office.com/en-gb/art...a-93f2b046f503

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Actually your makeUSDate function is working differently than you think
    From the Immediate window

    Code:
    ?makeUSDate(#11/01/2020#)
    11/1/2020
    ?makeUSDate(#15/01/2020#)
    1/15/2020
    I've slightly modified your GetDiff function and it now works (despite the above results):
    Code:
    Public Function GetDiff(xDate As Date, yDate As Date) As Integer
    xDate = makeUSDate(xDate)
    yDate = makeUSDate(yDate)
    GetDiff = DateDiff("d", xDate, yDate)
    End Function
    Example results:
    Code:
    ?GetDiff(#11/01/2020#, #15/01/2020#) 
    4 
    ?GetDiff(#11/01/2020#, #05/02/2020#)
     25 
    ?GetDiff(#02/01/2020#, #30/04/2020#)
     119
    Or of course you can just do this:
    Code:
    DateDiff("d", Format(StartDate, "mm/dd/yyyy"), Format(EndDate, "mm/dd/yyyy"))
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Pilot is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    3

    Issue with passing Date() to function

    Quote Originally Posted by isladogs View Post
    Actually your makeUSDate function is working differently than you think
    From the Immediate window

    Code:
    ?makeUSDate(#11/01/2020#)
    11/1/2020
    ?makeUSDate(#15/01/2020#)
    1/15/2020
    I've slightly modified your GetDiff function and it now works (despite the above results):
    Code:
    Public Function GetDiff(xDate As Date, yDate As Date) As Integer
    xDate = makeUSDate(xDate)
    yDate = makeUSDate(yDate)
    GetDiff = DateDiff("d", xDate, yDate)
    End Function
    Example results:
    Code:
    ?GetDiff(#11/01/2020#, #15/01/2020#) 
    4 
    ?GetDiff(#11/01/2020#, #05/02/2020#)
     25 
    ?GetDiff(#02/01/2020#, #30/04/2020#)
     119
    Or of course you can just do this:
    Code:
    DateDiff("d", Format(StartDate, "mm/dd/yyyy"), Format(EndDate, "mm/dd/yyyy"))
    __________________________________________________ __________________________________________________ ____
    Thanks for both suggestions, but the issue is with using the Date() as one of the dates to be passed to the GetDiff function.

    It would seem that there is no way to convert the Date() from the system format to US format that I have bee able to find. The solutions offered work for specific dates, but not where one of the dates is Date().

    OK, I have found the solution:

    Dim XL As Long
    XL = DateDiff("d", Date, xDate) ' where xDate is the date passed to the function.

    Works fine with system dates.

  5. #5
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    You dont need the get us date function if you use date() I dont think you'll need to format the date

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Mick is correct. The Date function handles this automatically
    Code:
    ?DateDiff("d", Date(), Format(#15/01/2020#, "mm/dd/yyyy"))
     3 
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Pilot is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    3
    Thanks for all the input. I achieved the result I was working towards, and a pic of the generated report appears below:
    Company Expiry.pdf

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

Similar Threads

  1. DateDiff returning inaccurate result?
    By yeah in forum Programming
    Replies: 2
    Last Post: 04-20-2018, 11:43 AM
  2. Query not returning correct results
    By badmem in forum Queries
    Replies: 6
    Last Post: 07-26-2016, 10:16 AM
  3. IIf function Not Returning Correct Calculation
    By MAFuser in forum Database Design
    Replies: 7
    Last Post: 08-13-2015, 06:46 PM
  4. DateDiff returning negative
    By Pauld in forum Queries
    Replies: 5
    Last Post: 10-12-2011, 10:23 PM
  5. Replies: 9
    Last Post: 06-26-2011, 09:14 PM

Tags for this Thread

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