[AccessD] Comparing date and time

Hi David
Well before you think about that how about this quick solution

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

OR (EntryLogSets.Date=[Forms]![BSVR]![BsvrStartDate] AND
EntryLogSets.Time>=[Forms]![BSVR]![BsvrStartTime])

ie the time only needs comparing if the dates are equal.

Just a quick thought on my way out.

-- Andy Lacey
http://www.minstersystems.co.uk 


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> David Emerson
> Sent: 23 November 2007 08:08
> 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
> 
>