[AccessD] Comparing date and time

Steve Schapel miscellany at mvps.org
Fri Nov 23 14:48:04 CST 2007


David,

If the [Date] and [Time] fields in the table are a Date/Time data type, 
and if the criteria controls on the form are formatted with a valid date 
and/or time format, then I don't think you would need the CDate and 
DateValue functions as you have used them.  I would expect (without 
having tested) that this would work:
"WHERE (EntryLogSets.Date + EntryLogSets.Time) Between (#" & 
[Forms]![BSVR]![BsvrStartDate] & "# + #" &
[Forms]![BSVR]![BSVRStartTime] & "#) And (#" & 
[Forms]![BSVR]![BsvrEndDate] "# + #" & [Forms]![BSVR]![BSVREndTime] & "#)"

I always wrap date and time criteria in CLng or CDbl to handle 
international problem, so I would do it like this:
"WHERE (EntryLogSets.Date + EntryLogSets.Time) Between (" & 
CLng([Forms]![BSVR]![BsvrStartDate]) + 
CDbl([Forms]![BSVR]![BSVRStartTime]) & ") And (" & 
CLng([Forms]![BSVR]![BsvrEndDate]) + CDbl([Forms]![BSVR]![BSVREndTime]) 
& ")"

Regards
Steve

David Emerson wrote:
> 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.
> 



More information about the AccessD mailing list