 |
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
|
|
|

April 22nd, 2005, 03:49 PM
|
Registered User
|
|
Join Date: Mar 2005
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Compare only the date portion of a datetime field
Experts,
I need to extract the date portion of a datetime column .
Sample SQL
---------
Select
"Project Sub ID",
"Staff Name",
"Status Reporting Date"
From
"Status"
Where
"Project Sub ID" = '1451291 Purge/Archival Processing' and
"Staff Name" = 'Pradhan, Ismail' and
"Status Reporting Date" = '04/15/2005'.
The "Status Reporting Date" is a datetime field , so it is stored as '04/15/2005 14:55:56".
In the above compare it does not match because it is comparing '04/15/2005 14:55:56" with '04/15/2005 00:00:00'.
Is there some built in function in Access I can apply to my SQL to get to only the date part of the "Status Reporting Date" field ?
Appreciate your help.
Thanks
|

April 23rd, 2005, 01:14 AM
|
Friend of Wrox
|
|
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Use format to remove the time part
Note:Dont forget to use the CDATE to convert back to date if you have to use logical opertors
EG: CDate(Format([Status Reporting Date],"mm/dd/yyyy")) = = '04/15/2005'.
|

April 23rd, 2005, 11:14 AM
|
Registered User
|
|
Join Date: Mar 2005
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Prashant,
Thank you for your help.
I will try first it thing in the morning Monday and let you know.
Thanks
|

April 25th, 2005, 09:56 AM
|
Registered User
|
|
Join Date: Mar 2005
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Parshant,
I tried your example and it did not work. I get a "syntax error in query expression" message.
Here is what I tried :
Select
"Project Id" as project_id,
"Project Sub Id" as project_sub_id,
"Staff Name" as staff_name,
"Status Reporting Date" as reporting_dt,
CDate( Format(["Status Reporting Date"],"mm/dd/yyyy") ) = '04/15/2005' ;
From
status ;
I am running this query against Microsoft Access 2002.
Also, I tried the quesry with "= = " also ( 2 equal signs ) , that did not work too. I thought the 2 equal signs was a Java thing.
I did a serach in Access help for CDATE and it came up completely blank.
I removed CDate and tried and got the same error
Select
"Project Id" as project_id,
"Project Sub Id" as project_sub_id,
"Staff Name" as staff_name,
"Status Reporting Date" as reporting_dt,
Format(["Status Reporting Date"],"mm/dd/yyyy") = '04/15/2005' ;
From
status ;
Can you see what I am doing wrong ?
Appreciate your help
|

April 25th, 2005, 11:49 AM
|
Registered User
|
|
Join Date: Mar 2005
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Parshant,
I also tried it this way
Select
"Project Id" as project_id,
"Project Sub Id" as project_sub_id,
"Staff Name" as staff_name,
"Status Reporting Date" as reporting_dt,
Format(["Status Reporting Date"],"mm/dd/yyyy") = #04/15/2005# ;
From
status;
I thought I needed the "#" for the date , but still no luck.
Thanks
|

April 26th, 2005, 08:12 AM
|
Friend of Wrox
|
|
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
|
|
The syntax you were given was for the WHERE clause, not the SELECT clause.
You need to name the field when you create a formated field in the SELECT part of your query like:
Format([Status Reporting Date],"mm/dd/yyyy") as reporting_dt
For your WHERE clause use either:
Format([Status Reporting Date],"mm/dd/yyyy") = "04/15/2005"
Or:
CDate(Format([Status Reporting Date],"mm/dd/yyyy")) = #04/15/2005#
Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
|

April 27th, 2005, 01:06 AM
|
Friend of Wrox
|
|
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I agree with Randall, and sorry for the "= =", It was typeing mistake.
|
|
 |