Bill Benson
bensonforums at gmail.com
Thu Aug 21 14:46:03 CDT 2014
I am really sorry I asked this one now! My question "what is meant by a GUI date just needed a yes or no to my illustration, to wit, " Is this a bound textbox reading a date from the back end?"; [or if not that, something else - and if so, what?].
Jim said:
<<GUI would be the FE application 'page/window display' facility.
I don't know what that is. I have never heard of anything so technical in answer to what I thought was a simple question, and that is why, if there are no other answers out there, I withdraw the question.
Jim further said the effect would depend on
<<1) the format the data is put into by the BE
I already posted:
<<my backend had Aug 4th 2014 stored as 08/04/2014
As I was discussing an Access only solution, I think I pretty much covered that this is an Access Date stored as 08/04/2014.
I cannot see how I could be clearer.
Jim paraphrases my lengthier question regarding SQL statements that incorporate the "#" delimiter and a direct reading of the value (as opposed to the text property) of
txtEffectivedate as
Re
#" & txteffectivedate & "#)"
#" & cdate(txteffectivedate) & "#)"
I had written (*emphasis* added):
> 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) & "#)"
I don't feel Jim has given me an answer and I am asking again for someone in this List to give a yes to one (and if so, which), yes to either, no to both (and if so, what is the right way).
Reason: Jim ventures:
<<If that is 'submitted' as a text query to a USA DBMS, expect to enter the values in MM/DD/YYYY format
I am not asking about a pass through query, so I don't know what the BE has to do with it. I am talking about DoCmd.RunSQL or CurrentDB.Execute and the like. Again, this Front End is a running European instance of MS Access and Interface. I am going to continue to assume that the FE is driving the inquiry, althout it continues to be unclear to me what the "value" really is at any point in time. This is new information being put into a record, and I believe that the back-end will have a different idea of what is meant by 04/08/2014 than the front-end will.
I cannot test this (though testing it would certainly be very simple if I had the right environment) because I don't have a European Front End to test it with. I guess I will not worry about it for the foreseeable future.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of James Button
Sent: Thursday, August 21, 2014 2:58 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] New thrd: dates
Bill,
GUI would be the FE application 'page/window display' facility.
Effect would depend on:
1) the format the data is put into by the BE, - probably determined by the OS, DBMS, and data extraction application being used.
2) The OS and application being used to select/request the data and then print/present it to the user/viewer
3) If the data is held as a date by the DBMS then it will be in an internal format without any display characteristics such as 4 digit year, month or day first whatever.
(the verbiage - but there is the possibility that the dates are held as test strings rather than as Access Dates.)
So the submission of selection criteria and data presentation will be according to the implicit definitions in the client PC OS and application setup, modified by the defaults associated by the particular versions of the OS & application, unless specifically conditioned by your handling, and format specification of the data.
Re
#" & txteffectivedate & "#)"
#" & cdate(txteffectivedate) & "#)"
If that is 'submitted' as a text query to a USA DBMS, expect to enter the values in MM/DD/YYYY format When data is returned it could be as the internal format, but is more likely to be in the format assumed by the BE - as in USA MM/DD/YYYY format.
A simple test such as (>#10/01/2104# and <#08/02/2104#) and then (>#08/02/2104# and <#10/01/2104#) should let you know what the setup is doing/using as the format.
Otherwise, sorry but in an unspecified environment it is not possible to give a specific answer.
It could be that the FE is itself a DBMS and is actually handling the data doing selection from a view of the USA based data
In other words - It depends on your setup !
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 6:58 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] New thrd: dates
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
>
--
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