Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
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 March 28th, 2005, 12:12 PM
Registered User
Join Date: Mar 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Syntax to query Date/Time in MS ACCESS

what is the syntax for query date/time in ms access...
"select date from table1 where pDate = #10/03/2005#"
it return me empty record. pls help

Old March 29th, 2005, 12:15 PM
ru1 ru1 is offline
Authorized User
Join Date: Feb 2005
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts

Is pdate defined as a "DATE" type in the table. Access will not return a good value if the table type is not "DATE" for pDate. It let's you use the "#" for dates, but doesn't return anything for a value.

Let me know if this was the issue.


Old March 30th, 2005, 08:52 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

RU1 has a point. Out of curiousity, is the name of your field and table really DATE and TABLE1? If so, change DATE to something else. Date() is an Access function and, therefore, reserved.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Old March 30th, 2005, 10:57 PM
Registered User
Join Date: Mar 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts

My actual attribute name is SNo, and table name is Staff.
thanks for your reply. i hav solve it by

FROM Staff
WHERE Format([ComplaintDate],"yyyy-mm-dd")>='2005-03-09'

Thank you very much!!!! :)

Old March 31st, 2005, 09:08 AM
Friend of Wrox
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

What you discovered is that a date time field in a table includes the time. So you weren't getting an exact match when you used the equal operator because of the time in the field.

In your last example you coule have done:

Where ComplaintDate >= #03/09/2005#

It's not likely that you'd ever get exactly equal to 03/09/2005.

BTW, if you wanted just 3/9 you'd code:

Where (ComplaintDate >= #03/09/2005#) and (ComplaintDate < #03/10/2005#)

Your Format function makes it easier to point to a single date.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert UNIX Time to MS Access Corey Linux 4 April 7th, 2009 11:52 AM
MS Date Time picker change Paulys72 Access 1 August 20th, 2007 06:21 AM
Syntax of date function in MS Access krishreddy Access 3 July 25th, 2006 03:42 PM
Convert UNIX Time to MS Access Corey Access 1 January 23rd, 2006 12:32 AM
Getting Problem in DATE/TIME Data type in MS Acces selam70 Classic ASP Databases 2 September 4th, 2004 02:31 PM

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