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?