Bill Benson
bensonforums at gmail.com
Thu Aug 21 12:58:14 CDT 2014
Too much for me to understand, sorry. If my examples were simple perhaps an
direct answer based on my examples would be helpful. If my examples were
not clear, I could try to restate. All that verbiage about standards should
be irrelevant as I am asking what Access will do natively based on a
backend sitting on a US server and a FE running in a European office which
is attached across a network.
On Aug 21, 2014 12:30 PM, "James Button" <jamesbutton at blueyonder.co.uk>
wrote:
> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>