[AccessD] Comparing date and time

Thanks for the explanation Drew - it is always nice to know why I am 
doing something :-)

At 27/11/2007, you wrote:
>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.
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com