Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 17th, 2003, 04:17 PM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 121
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date from timedate stamp

I have a simple Access table and one field is a timedate stamp.
I wish to retrieve only the date value ( the value and not
a formatted date string).

ie select mydate from mytable

this just gives me for example 01/10/2003 00:00:00

how do I do a select that just gives me 01/10/2003 ?

Any help appreciated
 
Old November 17th, 2003, 05:30 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If you are trying to get the value out of a query. do as follows

In design view on the query, click on "field" on the field that you wish to change (your date field from the table).
Click on properties on the toolbar
on the general tab opf properties, click on format.
select "Short Date" from the drop down.

You can do the same in reports and forms.

Does this help?






Sal
 
Old November 18th, 2003, 04:46 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 121
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Unfortuantely I cannot change the field in the database to be
a shortdate because other applications write a full datetime into this field. I need an Access syntax that only retrieves
the date part of the datetime field.

 
Old November 18th, 2003, 05:29 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

As Access holds dates as double precision numbers, there is no such thing as just a date field, or just a time field.

To illustrate this, try the following in the immediate window of the VBE:

?cdbl(time())
?cdbl(Date())
?cdbl(Now())

?cdate(1345.5433)
?cdate(0.5433)
?cdate(1345.0)

So, the formatted date string is your best bet really.


Brian Skelton
Braxis Computer Services Ltd.
 
Old November 18th, 2003, 10:33 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can try this

Inside the design view of a query create a field and enter this

x: Format([YourDateField],"mm/dd/yyyy")

Substitute YourDateField with the name of your field.
This will create a field with the title of "x" that only has the date you need.

Then if you where looking in the query for all entries with the date of today do this
under the criteria

#11/18/2003#

Does this help?



Sal
 
Old November 19th, 2003, 07:59 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 121
Thanks: 0
Thanked 0 Times in 0 Posts
Default


Hi sal,

yep that does it fine
Many thanks

Chas.






Similar Threads
Thread Thread Starter Forum Replies Last Post
insertor update with currend date stamp garg SQL Server DTS 1 October 23rd, 2006 08:10 AM
store insert/update date/time stamp in sql2k db alexdcosta ASP.NET 2.0 Basics 0 June 22nd, 2006 05:44 AM
Inserting Time Stamp into SQL testsubject ADO.NET 1 May 19th, 2006 07:14 AM
Date/Time Stamp on Reports dillig BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 August 11th, 2004 08:21 AM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.