[AccessD] Comparing date and time

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