[AccessD] New thrd: dates

Good point.  You are correct of course, Datevalue()  =  INT(CDate())

I wasn't thinking about the time part when I said that.  I was just thinking about storing dates 
and comparing dates, not timestamps.  



-- 
Stuart

On 22 Aug 2014 at 13:53, Heenan, Lambert wrote:

> Actually DateValue() and CDate() are different. Cdate will convert
> anything that looks like a date (such as a string) to a Date type
> value.  DateValue will also convert strings to dates, but its real
> purpose is to return *JUST* the date. So if the data you pass to it
> includes a time stamp, you get back just the date, as in...
> 
> ? datevalue("8/22/2014 9:50:30 AM")
> 8/22/2014
> 
> Lambert
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
> McLachlan Sent: Thursday, August 21, 2014 7:41 PM To: Access
> Developers discussion and problem solving Subject: Re: [AccessD] New
> thrd: dates
> 
> No reason other than habit.  They both do exactly the same thing as
> far as I can tell.
> 
> On 21 Aug 2014 at 19:06, Bill Benson wrote:
> 
> > Love - and am deeply grateful for, Stuart, the simplicity in your
> > answer. Loved even more "I always use" w/o problems, that means
> > field tested. Any reason you use DateValue rather than CDate?
> > 
> > 
> > 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
> > McLachlan Sent: Thursday, August 21, 2014 5: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
> > 
> 
> 
> -- 
> 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
>