Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    1

    Unanswered: Error in ms access days count

    Dear All
    Expecting a solution to rectify my problem.
    Document_Date Days_Count
    07-Aug-13 -26 (Actual date is 08/07/2013)
    04-Oct-13 -84 (Actual date is 10/04/2013)

    28-Jun-13 14
    09-Jun-13 33
    04-Jun-13 38
    04-Jun-13 38


    The above Days count is caluclated with System Date (i.e. 12/07/2013)
    But some days count shows wrongly as Minus & I found the above field date
    taken as mm/dd/yyyy instead of dd/mm/yyyy. My table format date is dd/mm/yyyy & I had input also dd/mm/yyyy format.

    My present query is :

    SELECT DIS_MASTER.WH_DIS_DATE,
    DateDiff("d",[DIS_MASTER.WH_DIS_DATE ],Now()) AS Days_Count
    FROM (DIS_MASTER LEFT JOIN [SUP-MAST] ON DIS_MASTER.LINE = [SUP-MAST].LINE_CODE) INNER JOIN DIS_DETAILS ON DIS_MASTER.DISNO = DIS_DETAILS.[NO]
    WHERE (((DIS_DETAILS.SettlementDetails) Is Null));

    Please inform the solution to rectify the same.
    Last edited by tpracsg; 07-13-13 at 01:23.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Short explanation: When working with dates in Access SQL, you have to present the dates to Access in US Format.

    For the long explanation, as well as detailed instructions on making this work, you'll need to read Allen Browne's excellent articles on International Dates:

    Microsoft Access tips: International Dates in Access

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access is US centric, but its also (in this area ISO complaint so I'd suggest you get in the habit of using ISO dates "YYYY/MM/DD".

    If you are using the DD/MM/YYYY as a display format then I;'d suggest you check your computer and or MS Office localisation settings and makje certain they are appropriate.

    also be careful on choosing whihc value to use for the current system date
    date() returns the current system date
    now() returns the current system date AND time
    the only effective difference is that date() has a time of 00:00:00
    its not an issue UNLESS you are doping date calculations with columns that inlcude time vlaues as it can cause problems.

    format (date(),"YYYY/MM/DD") is identical to format (now(),"YYYY/MM/DD").
    but tyhe underlyign atual values are different.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •