[AccessD] Comparing date and time

Thanks Andy,

I have simplified the problem.  Ultimately I need to compare two sets 
of dates and times to get all records that fall between one date and 
time, and a second date and time.

I could use your suggestion and do similar comparisons with the end 
date and time as well.  I would be interested though in finding out 
how to create a full date/time from two separate fields.

David

At 23/11/2007, Andy Lacy wrote:
>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