[AccessD] Comparing date and time

Thanks to everyone who replied.

The final solution was twofold.  First change the query:

DoCmd.RunSQL "INSERT INTO ShipmentDetailForBSVRperiod SELECT 
ShipmentDetail.* " & _
"FROM ShipmentDetail INNER JOIN EntryLogSets ON 
ShipmentDetail.CargoID = EntryLogSets.CargoID " & _
"WHERE (CDate(DateValue(EntryLogSets.Date) + 
TimeValue(EntryLogSets.Time))>= 
CDate(DateValue([Forms]![BSVR]![BsvrStartDate]) + 
TimeValue([Forms]![BSVR]![BSVRStartTime]))) " & _
"And CDate(DateValue(EntryLogSets.Date) + 
TimeValue(EntryLogSets.Time))<= 
CDate(DateValue([Forms]![BSVR]![BsvrEndDate]) + 
TimeValue([Forms]![BSVR]![BSVREndTime]))"


Secondly, tidy up the data to remove invalid entries in the time field.

David

At 23/11/2007, you wrote:
>Well this principel works
>
>(from Immediate window)
>
>x=datevalue("31/12/07") + timevalue("11:56:00")
>? format(x,"dd/mm/yy hh:nn:ss")
>31/12/07 11:56:00
>
>
>--
>Andy Lacey
>http://www.minstersystems.co.uk
>
>
>
>--------- Original Message --------
>From: "Access Developers discussion and problem solving"
><accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem solving"
><accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Comparing date and time
>Date: 23/11/07 08:42
>
>
>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
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>________________________________________________
>Message sent using UebiMiau 2.7.2
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com