[AccessD] New thrd: dates

I'd expect a reference to a GUI means graphical User interface - as in a form or other form of data presentation/input display that is not at 'programmer test-data file/table level)
-------------------------------------------
Your example 
Aug 4th 2014 stored as 08/04/2014.
Is it actually stored as the string "08/04/2014"

As in 
Oracle stores DATE in total of 7 bytes. Each byte in it stores values for an element of the DATE as follows:
Byte Description 
1 Century value but before storing it add 100 to it 
2 Year and 100 is added to it before storing 
3 Month 
4 Day of the month 
5 Hours but add 1 before storing it 
6 Minutes but add 1 before storing it 
7 Seconds but add 1 before storing it

Access stores Date/Times internally stored as an 8 byte double precision floating point numbers.
So the range is virtually unlimited. (Dates up to 2 million AD can be stored with a precision of 1 second.) MDB Viewer exports dates in the format YYYY-MM-DD HH:MM:SS.
--------------------------------------------

The problem should not be in dealing with date(time) values in a database as that should - Ha! SHOULD have been validated on input.
And presumably (! again) have been stored using the DBMS's  internal date (day number) format.

Adopt 'standards' (and ask the client what standards they prefer if the ones you have are not acceptable.)
As in 
Display dates with 4 digit years, and the month in alpha (3 char - or if you are really tight on space - 2 char), which also allows you to lose separators.  If possible use a calendar to allow the user to select the date - avoids any (! Again) confusion on the part of the user.
If you cannot use a calendar selection for input, then require the date to be entered with month as a 3 letter alpha (using the set for the country) and validate that, converting it to the internal storage - as in specifically using the US/ISO format.
Or if you cannot constrain them to alpha month, display the entered date using the alpha month

As far as searching data in the DBMS - that should, if it is to be held as a date,  be in the internal format, so as long as you are specific in the definition of the date you are specifying to be found, there should be no problem.

Then there are the things like invoices - where you want the actual entry as shown, and do not want the system to convert the reference code of "01/02" or "01/02/14", or "01/02/2014" into dates, and then - later someone decides they want to search for invoices with a specific reference and they assume it was a date.
Even more fun invoices 1 to 5 of a set for ongoing action, shown with 1/5/  2/5/  3/5/  etc. as prefix's to the reference number.

It's fun out there in commerce and industry !

JimB


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Thursday, August 21, 2014 4:13 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] New thrd: dates

Please forgive my lack of experience, also I thought since this is a deeper
discussion on dates I would retain some but not all of the former posts.

GUI. What is meant by GUI date? Is this a bound textbox reading a date from
the back end?

Example, my backend had Aug 4th 2014 stored as 08/04/2014. In the local
environment with no local formatting, would that display as
04/08/2014 In Europe?

Now supposed unbound forms (which are what primarily work with)

If I populate a text box with

TXTLastChange = DLOOKUP ("Max(Change_dt)","MyTable","")

And the max date in the table is 08/10/2014, what will show in the local
(Europe) text box?, I assume based on what has been said, 10/08/2014, no?

If suppose I have another text box called TxtEffective_dt.  The user wants
to enter Aug 12th, 2014 so thwy, being in Europe, enter 12/08/2014 or
12/08/14.

When I take that value to enter it into the database (remember backend is
US) would I write

Sql = "Insert into myTable (name, address, effective_dt) values ('" &
txtname & "','" & txtaddress & "', #" & txteffectivedate & "#)"

Or
Sql = "Insert into myTable (name, address, effective_dt) values ('" &
txtname & "','" & txtaddress & "', #" & cdate(txteffectivedate) & "#)"

Or something else that will have to convert the text property of
txteffectivedate to the US format?

>
> On Aug 21, 2014 10:43 AM, "Gustav Brock" <gustav at cactus.dk> wrote:
>>
>> Hi Bill
>>
>> The workaround in SQL code is always to use the ISO format: yyyy-mm-dd.
>> It will never fail.
>>
>> As for the GUI, it never fails as long as you follow the simple rules
mentioned.
>>
>> /gustav
>>
>> -----Oprindelig meddelelse-----
>> Fra: accessd-bounces at databaseadvisors.com [mailto:
accessd-bounces at databaseadvisors.com] På vegne af Bill Benson
>> Sendt: 21. august 2014 16:36
>> Til: Access Developers discussion and problem solving
>> Emne: Re: [AccessD] Most common problems/situations
>>
>> I have always worked in US so I don't think I have ever run into this.
But, if there is SQL looking for 08/04/2014 and in the data there is no
08/04/2014, but there happens to be a 04/08/2014, and the user's local date
format is Europe, will a match on  04/08/2014 be returned? What would the
workaround be if your US database BE has an Access FE being used in
European environment?
>> On Aug 21, 2014 9:29 AM, "Gustav Brock" <gustav at cactus.dk> wrote:
>>
>> > Hi Jack
>> >
>> > There is no "sometimes". In the GUI, the date format is always
>> > localized except if you specify another format in the Format property.
>> > In VBA and SQL, date string expressions are always read in US, then
>> > local, then ISO format until a match.
>> > For CDate and DateValue, however, the sequence is local, US, ISO.
>> > For ADO and FindFirst, only the ISO format is reliable.
>> >
>> > /gustav
>> >
>> > -----Oprindelig meddelelse-----
>> > Fra: accessd-bounces at databaseadvisors.com [mailto:
>> > accessd-bounces at databaseadvisors.com] På vegne af jack drawbridge
>> > Sendt: 21. august 2014 14:30
>> > Til: Access Developers discussion and problem solving
>> > Emne: Re: [AccessD] Most common problems/situations
>> >
>> > Arthur,
>> >
>> > Thought I'd pass this on since it came by today and seems to fit your
>> > request.
>> >
>> > " In the user interface - forms, query criteria, - where users enter
>> > dates, MS assumes the format is the system setting, even if the date
>> > is enclosed in # tags, as it might be in query criteria. I have always
>> > been led to believe that any date between # marks had to be MDY
>> > (regardless of system setting), but no. Only sometimes.
>> >
>> > You'd think MS could enable users to set the date format that Access
>> > uses everywhere, including SQL and VBA, regardless of the system date
>> > format setting.
>> >
>> > I wonder how many non-USA users have been caught by this, without
>> > realizing it? "
>>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com