[AccessD] New thrd: dates

Amongst the many posts on the web about dates this one may be appropriate to
this discussion - the pain to consider and avoid
http://allenbrowne.com/ser-36.html

and from MS - the options 
<https://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us
/country.mspx?mfr=true>

There are probably a win8 and office 2013 365 etc. versions but that link was in
my histerical notes. 

JimB

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

OK, here goes as asked for (an Access FE/BE):

Answers in line.

On 21 Aug 2014 at 15:46, Bill Benson wrote:

> 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?].
> 

Yes.   It's the formatted date as displayed in a control on a form or report.

> 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

No, your date is NOT stored iin an Access BE as 08.04/02014.  It is stored as
the number 41855.0   which is the number of days since 30 Dec 1899.

> 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) &
> > "#)"

It doesn't matter whether the BE is US or UK, the date is stored as the same
number.  

The problem is that the SQL engine in the Access FE requires a date surrounded
by "#"s to 
be in a specific format, which may not be the format in which txtEffectiveDate
is being 
displayed. (In a European FE, the txtEffectiveDate could be 08/04/2014,
04/08/2014,"04 Aug 
2014","Aug 4 2014" , "Auot 4 2014", or many other language specific formats.)


There are two common solutions:

 Sql = "Insert into myTable (name, address, effective_dt) values ('"
 & txtname & "','" & txtaddress & _
"', #" & format(txteffectivedate,"mm/dd/yyyy") & "#)"

or

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

FWIW, I always use the second version and have never had a problem with it.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com