[AccessD] Comparing date and time

All the results of Format() are strings.

Susan H.



I had a problem last week using FORMAT when building dates from text.  It 
displayed the dates correct, but sorted them as text, and filtered them that 
way...I could even use DATEPART, but they still were not dates...just text.

I ended up using CDATE to get what I needed.

Just food for thought.

Thanks,

Mark A. Matte


> Date: Fri, 23 Nov 2007 10:50:36 +0100
> From: andy at minstersystems.co.uk
> To: accessd at databaseadvisors.com
> Subject: Re: [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"
>
> To: "Access Developers discussion and problem solving"
>
> 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

_________________________________________________________________
Your smile counts. The more smiles you share, the more we donate. Join in.
www.windowslive.com/smile?ocid=TXT_TAGLM_Wave2_oprsmilewlhmtagline
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com