Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 17th, 2003, 04:17 PM
Friend of Wrox
 
Join Date: Sep 2003
Location: Whitstable, , United Kingdom.
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
Reply With Quote
  #2 (permalink)  
Old November 17th, 2003, 05:30 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
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
Reply With Quote
  #3 (permalink)  
Old November 18th, 2003, 04:46 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Whitstable, , United Kingdom.
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.

Reply With Quote
  #4 (permalink)  
Old November 18th, 2003, 05:29 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Oxford, , United Kingdom.
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.
Reply With Quote
  #5 (permalink)  
Old November 18th, 2003, 10:33 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
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
Reply With Quote
  #6 (permalink)  
Old November 19th, 2003, 07:59 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Whitstable, , United Kingdom.
Posts: 121
Thanks: 0
Thanked 0 Times in 0 Posts
Default


Hi sal,

yep that does it fine
Many thanks

Chas.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:34 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.