[AccessD] Comparing date and time

David Emerson newsgrps at dalyn.co.nz
Fri Nov 23 02:07:41 CST 2007


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?





More information about the AccessD mailing list