Thread: Error in ms access days count
07-12-13, 13:00 #1Registered User
- Join Date
- Jul 2013
Unanswered: Error in ms access days count
Expecting a solution to rectify my problem.
07-Aug-13 -26 (Actual date is 08/07/2013)
04-Oct-13 -84 (Actual date is 10/04/2013)
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 :
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 00:23.
07-13-13, 23:17 #2Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
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
07-15-13, 06:20 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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