[AccessD] New thrd: dates

James,

That's one of the pieces to which Gustav responded in the original thread.


jack


On Thu, Aug 21, 2014 at 6:28 PM, James Button <jamesbutton at blueyonder.co.uk>
wrote:

>
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>