[AccessD] Comparing date and time

Drew Wutka DWUTKA at Marlow.com
Mon Nov 26 09:51:49 CST 2007


Sorry to chime in here late, but if these are true date/time fields,
then you need to add them, not 'join' them.  In a date/time field, the
data is stored as a double, the integer portion is the date, the decimal
portion is the time.  To join a date and time together, you just add
them.  Joining them like text requires that they are represented by text
(which is more work, because you are converting from numbers to text,
then back to numbers)

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
Sent: Friday, November 23, 2007 2:08 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Comparing date and time

Group,

Sorry to interrupt your feasting :-)

I have a legacy database that stores times and dates in two separate 
date/time fields.  Currently there is a query that compares two sets 
of dates like so:

DoCmd.RunSQL "INSERT INTO ShipmentDetailForBSVRperiod SELECT 
ShipmentDetail.* " & _
"FROM ShipmentDetail INNER JOIN EntryLogSets ON 
ShipmentDetail.CargoID = EntryLogSets.CargoID " & _
"WHERE (EntryLogSets.Date)>=[Forms]![BSVR]![BsvrStartDate])"

This works.  However, I need to change it so that it gets records 
that are after a certain date AND TIME.  I am having problems with 
putting the two fields together into a single date/time.  Here is my
code:

DoCmd.RunSQL "INSERT INTO ShipmentDetailForBSVRperiod SELECT 
ShipmentDetail.* " & _
"FROM ShipmentDetail INNER JOIN EntryLogSets ON 
ShipmentDetail.CargoID = EntryLogSets.CargoID " & _
"WHERE CDate(EntryLogSets.Date  & ' ' &  EntryLogSets.Time)>= #" & 
CDate([Forms]![BSVR]![BsvrStartDate] & " " & 
[Forms]![BSVR]![BSVRStartTime]) & "#"

An example of CDate([Forms]![BSVR]![BsvrStartDate] & " " & 
[Forms]![BSVR]![BSVRStartTime]) is 28/09/2007 9:40:00 a.m.

The error I get is Error 3075 - Syntax error in date in query 
expression.  The query runs ok if I just include the dates, but has a 
problem when I add the times.

How can I combine the two fields into a single date/time for comparison?


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI BusinessSensitve material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.





More information about the AccessD mailing list