UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Datediff Calculation Inconsistent, Access 2016    
 
   
projecttoday
post Aug 10 2017, 10:09 AM
Post#21


UtterAccess VIP
Posts: 8,604
Joined: 10-February 04
From: South Charleston, WV


If my understanding is correct, river's input contains 8/9/2017 which is interpreted as August 9 when the intention is September 8.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
River59
post Aug 10 2017, 10:44 AM
Post#22



Posts: 1,318
Joined: 7-April 10
From: Detroit, MI


I sure opened a can of worms here didn't I ... ohyeah.gif

Bruce, that is how I see it. If Argentina is using their regional settings and creates an Excel file, then all dates within the database should show that format. That means on forms, in reports, etc. Therefore, I don't understand what their problem is?
I think that I will change the settings on my computer, create a file, import it into my database and see what happens.

I do understand that the countries who use our file are stuck with our formatting unless I code everything to format to their region. The table has the date fields formatted as short date. There is no pleasing everyone in this scenario. It just is what it is ...

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
River59
post Aug 10 2017, 11:50 AM
Post#23



Posts: 1,318
Joined: 7-April 10
From: Detroit, MI


I just changed the regional settings on my computer and selected the date format, imported one of our system files, and all of the dates are in the selected format. Now I'm wondering what the fuss was about?
Next test will be to create a file and manually input dates. In a short test, looks like they have to be typed in Excel in US format but then the database will show and print everything out in whatever regional format I selected.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
BruceM
post Aug 10 2017, 11:52 AM
Post#24


UtterAccess VIP
Posts: 6,989
Joined: 24-May 10
From: Downeast Maine


As I mentioned, if you don't apply formats the dates should appear in the user's default short date format. In other words, rather than formatting everything, format nothing unless needed for a specific display purpose.
Go to the top of the page
 
PhilS
post Aug 10 2017, 12:02 PM
Post#25



Posts: 397
Joined: 26-May 15
From: The middle of Germany


@projecttody + @BruceM:
Any dates entered in Access (and Excel) will be parsed according to the Regional Settings of the computer automatically.
If you enter 8/10/2017 and have US regional settings configured, this will be the 10th of August 2017.
If you enter 8/10/2017 and have Argentinian regional settings configured, this will be the 8th of October 2017.

As long as every user has the Regional Settings configured to match the date format he is actually using, there is no ambiguity and no problem at all!

There also is no problem with exchanging files, as long as the dates are explicitly stored as a Date data type.
I Attached two screenshots of an excel file. It is the same file. I just changed my Regional Settings from "German (Germany)", which is what I am using, to "English (United States)".
I did not change the file in any way!
Attached File  de_de_dateformat.png ( 5.84K )Number of downloads: 0
Attached File  en_us_dateformat.png ( 6.63K )Number of downloads: 0


There will however be a problem if you use a text-based data exchange format like CSV. Then you need to custom code your application to use a specific date format independent of the regional settings for any import/export operation.
This post has been edited by PhilS: Aug 10 2017, 12:06 PM

--------------------
An excursion to the raw COM API with VBA: How to hide the Taskbar Button of a window.
Go to the top of the page
 
BruceM
post Aug 10 2017, 12:56 PM
Post#26


UtterAccess VIP
Posts: 6,989
Joined: 24-May 10
From: Downeast Maine


QUOTE
there is no ambiguity and no problem at all

The OP reported real problems, and there most certainly are issues when working with international dates. While I believe you are right about data entry of dates being interpreted correctly according to the use's regional settings (and I may have misstated the issue), it is going too far to say there is no potential for problems.
Go to the top of the page
 
PhilS
post Aug 10 2017, 01:37 PM
Post#27



Posts: 397
Joined: 26-May 15
From: The middle of Germany


QUOTE
The OP reported real problems, and there most certainly are issues when working with international dates.

Yes, the OP reported real problems. But maybe you missed the key point causing his problems.
Quoting River59:
QUOTE
the data type is text.

In that case there will be problems for sure. That's why I always go on about "store dates in a Date data type".

I work with "international dates" all the time, for a long time. - The problems are limited to text representations of the dates, be it either in a text-based file or on printed paper.

Now, there is another can of worms, you need to be aware of. Timezones! - That one contains a different breed of beasts altogether. But I'm not opening that can up today. ;-)

--------------------
An excursion to the raw COM API with VBA: How to hide the Taskbar Button of a window.
Go to the top of the page
 
BruceM
post Aug 10 2017, 02:00 PM
Post#28


UtterAccess VIP
Posts: 6,989
Joined: 24-May 10
From: Downeast Maine


Please reread the entire thread. I discussed date/time fields in some detail.
QUOTE
The problems are limited to text representations of the dates

That can be a significant problem when using dates as query parameters, for one example.
Go to the top of the page
 
projecttoday
post Aug 10 2017, 03:47 PM
Post#29


UtterAccess VIP
Posts: 8,604
Joined: 10-February 04
From: South Charleston, WV


Okay, so if importation is done correctly into the the date data type, you won't have the August 8-September 9 problem. Agreed?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
PhilS
post Aug 11 2017, 03:05 AM
Post#30



Posts: 397
Joined: 26-May 15
From: The middle of Germany


QUOTE
Please reread the entire thread. I discussed date/time fields in some detail.

I did so.
You did an excellent job, covering many aspects of date data handling!
However, I didn't find anything in particular I missed before.

Just one thing. You wrote, Access uses the Short Date format when no explicit format is set. This is not entirely correct. It uses the General Date format. The difference is that General Date displays time as well unless the time part is exactly 00:00:00.

QUOTE
QUOTE
The problems are limited to text representations of the dates

That can be a significant problem when using dates as query parameters, for one example.

Bruce, this sent me into fits of uncontrollable laughter. - Sorry, no offense intended!
That's what I would have considered lesson 1.1 in Access-Programming with dates. - But of course, from your (US) perspective this is well worth mentioning. Thank you for that!

Maybe I should mention (again) that I'm based in Germany and naturally use German date formats. If you consider this to be handling "international dates" already, than every single Access project I ever worked on had to handle this. To me this is absolutely basic and natural. However, if your users will be only using the US date format, you probably got away with skipping explicit formatting for dates in code. - So, once again thank you for mentioning this.

My advice to everyone and to River59 (the OP) in particular:
Whenever formatting dates as text in a code-only context (e.g. for use in SQL strings) use the international ISO date format. YYYY-MM-DD
CODE
format(theDateValue, "yyyy-mm-dd hh:nn:ss")

(You can leave out the time part if it is not required)
This does not leave room for ambiguity.
It is very well suited for data exchange in text based formats (e.g. CSV) as well. I recommend you use this whenever the slightest possibility exists that source and target of a data transfer use different date formats by default.

It might even be the best solution for displaying/printing dates on reports if you cannot be sure what date format the reader/recipient expects. It might feel a bit uncommon to the reader, but that is much better than using a familiar format that might mean something entirely different to the reader that it actually intended (dd/mm vs. mm/dd).

--------------------
An excursion to the raw COM API with VBA: How to hide the Taskbar Button of a window.
Go to the top of the page
 
BruceM
post Aug 11 2017, 06:51 AM
Post#31


UtterAccess VIP
Posts: 6,989
Joined: 24-May 10
From: Downeast Maine


I almost invariably use something like Allen Browne's SQLDate function as described here (except I use the yyyy/mm/dd format instead of mm/dd/yyyy) when assembling SQL in VBA code, and things like that. Allen is Australian, and "international dates" is the expression he used, so I have been using it ever since. For calculating, say, the difference between StartDate and DueDate (two date/time fields), or calculations using Date or Now, there is no need, of course.

River59 initially spoke of using text values, but then it seemed to me he was using date/time values in practice but had tried using text as an experiment. In any case, I sort of merged the two concepts together when I spoke of data entry of date/time values. What I said should apply only if the date is entered as text, which would include formatted date/time values. I'm not sure if applying formatting as a text box property (as opposed to using the Format function) has the same effect. I always name the controls differently than the fields to which they are bound, and whenever possible I reference the field to which a control is bound rather than the control itself, so if there are any issues with using the Format property for a date/time text box I avoid them in that way.

It has become my habit to render dates as dd-mmm-yyyy (11-Aug-2017) for most purposes, as it is unambiguous. We work with companies from other countries, and it is annoying not to know for sure what date format is being used when the day of the month is <=12. Sometimes it is clear (if a revsion date is in the future or the day of the month is <=12, for instance), but otherwise I sometimes have to look for date values in the revision record or something to verify the format being applied. I take the same approach with documents that need to go to other countries -- avoid ambiguity and the possibliity of misunderstanding.

As for General Date and Short Date, you are correct. I was considering the regional settings, which includes only Short Date, Long Date, Short Time, and Long Time. There is no General Date, at least not in Control Panel. General Date, for U.S. settings anyhow, is a combination of Short Date and Long Time. My point was only that the users should see the dates in their chosen format unless another specific format has been applied.
Go to the top of the page
 
PhilS
post Aug 11 2017, 08:28 AM
Post#32



Posts: 397
Joined: 26-May 15
From: The middle of Germany


Bruce, I fully agree with your comments!

Just one thought.
I still would favor the ISO (yyyy-mm-dd) format over dd-mmm-yyyy for reporting purposes for the following reasons:
  • It is the official standard date format in a growing number of countries (Trivia: The ISO format is the official date format in Germany for over 21years now. Almost everyone is still using the traditional dd.mm.yyyy though.)
  • It one letter shorter. Might be relevant for data-dense lists
  • it is language independent
  • It still sorts correctly as text

--------------------
An excursion to the raw COM API with VBA: How to hide the Taskbar Button of a window.
Go to the top of the page
 
BruceM
post Aug 11 2017, 08:55 AM
Post#33


UtterAccess VIP
Posts: 6,989
Joined: 24-May 10
From: Downeast Maine


I would favor the ISO version in most cases too, but it is a gradual process getting users to accept it.
Go to the top of the page
 
River59
post Aug 11 2017, 09:32 AM
Post#34



Posts: 1,318
Joined: 7-April 10
From: Detroit, MI


Yesterday I tested by setting my pc to the date format for 4 different regions and it worked fine. Each time I changed my Regional Settings to another country and imported the Excel file, all of the numbers changed format (using ShortDate). This seems to solve all of the issues.

Monday I will do a quick calc on the dates (there are only 2 simple calculations to do) and see what happens. Somehow I think they will work.

Just as a note, I tried opening the Excel file with the new Regional Setting and even though you see the reformatted date, 21/3/2017, when you click on the cell, you see 3/21/2017. I even deleted a column of dates, saved and closed the file, then re-opened it and typed dates directly into the worksheet in that region's format. This file was imported and I compared all of the output and the dates were just what I expected.

This is the first time I have worked with a global application so this all of the help and education I have received here is greatly appreciated.
I believe this problem is solved.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
BruceM
post Aug 11 2017, 09:41 AM
Post#35


UtterAccess VIP
Posts: 6,989
Joined: 24-May 10
From: Downeast Maine


Glad to hear it. Sorry if I misled you about data entry of dates. As long as the data entry is correct according to the user's regional settings (or if you enforce a format), the date should be recorded correctly. It is only when working with dates as text values that extra attention is needed.
Go to the top of the page
 
River59
post Aug 12 2017, 09:00 AM
Post#36



Posts: 1,318
Joined: 7-April 10
From: Detroit, MI


Bruce, no misleading. All of the information received here was a good education. I just find it funny that I made it such a big problem when there was a simple solution.
Thanks again to everyone who put in their thoughts!

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
BruceM
post Aug 14 2017, 06:39 AM
Post#37


UtterAccess VIP
Posts: 6,989
Joined: 24-May 10
From: Downeast Maine


It sounds like you got it sorted out. If so, best of luck with the rest of the project.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    20th November 2017 - 12:07 PM