[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? "
>>