[AccessD] Comparing date and time

David,

Another thought...  It would arguably be neater if you put a couple of 
unbound textboxes on the form, with control sources:
=[BsvrStartDate]+[BSVRStartTime]
=[BsvrEndDate]+[BSVREndTime]

Let's say you name these textboxes BSVRStartDateTime and BSVREndDateTime.

So then we could have:
"WHERE (EntryLogSets.[Date] + EntryLogSets.[Time]) Between " & 
CDbl([Forms]![BSVR]![BSVRStartDateTime])  & " And " & 
CDbl([Forms]![BSVR]![BSVREndDateTime])

Or, assuming your code is actually running from an event within the BSVR 
form:
"WHERE (EntryLogSets.[Date] + EntryLogSets.[Time]) Between " & 
CDbl(Me.BSVRStartDateTime)  & " And " & CDbl(Me.BSVREndDateTime)

Regards
Steve


Steve Schapel wrote:
> 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.
>>