comparing dates

  • Thread starter Thread starter Juana
  • Start date Start date
J

Juana

Hi -
The query returns drivers assigned to open trips, the
tripID numbers, the trip depart date and return date.
If the selected driver is assigned to an open trip that
falls between the depart date and return date of
the new trip, the system is to return an error message.
How do I compare the form's requested departure date with
the query's depart date and return date? My code is as
follows:

Dim db As Database
Dim qry As Recordset
Dim frm As Form

Set frm = Forms![frmTransportraxxTripRequestLog].Form
Set qry = db.OpenRecordset("SELECT
qryDriverStatus.DriverID, qryDriverStatus.DistrictID," _
& "qryDriverStatus.Driver,
qryDriverStatus.DriverStatus," _
& "qryDriverStatus.Assigned,
tblAssignedDriversVehicles.[Trip#]," _

& "qryTripRequest1.TripRequestDepartureDate AS Depart," _

& "qryTripRequest1.TripRequestReturnDate AS Return" _
& "FROM (qryDriverStatus INNER
JOIN tblAssignedDriversVehicles" _
& "ON qryDriverStatus.DriverID =
tblAssignedDriversVehicles.[Driver#])" _
& "INNER JOIN qryTripRequest1 ON
tblAssignedDriversVehicles.[Trip#] =
qryTripRequest1.TripRequestID;")

If Not (qry.BOF And qry.EOF) Then
qry.MoveFirst

Do Until qry.EOF
qry.FindFirst "[DriverID] = " &
Me.ComboDriver.Column(0)
'the query returns drivers assigned to open
trips, the tripID numbers, the trip depart date and
return date.
'If the selected driver is assigned to an open
trip that falls between the depart date and return date of
'the new trip, the system is to return an error
message.

Loop
End If
 
Add a WHERE clause to your SQL statement along the lines of

"WHERE qryTripRequest1.TripRequestDepartureDate >= " &
Format(Me.txtRequestedDate, "\#mm\/dd\/yyyy\#") &
" AND qryTripRequest1.TripRequestReturnDate <=" &
Format(Me.txtRequestedDate, "\#mm\/dd\/yyyy\#")

If there's a chance that you won't have a requested date on your form, use

"WHERE (qryTripRequest1.TripRequestDepartureDate >= " &
Format(Me.txtRequestedDate, "\#mm\/dd\/yyyy\#") &
" AND qryTripRequest1.TripRequestReturnDate <=" &
Format(Me.txtRequestedDate, "\#mm\/dd\/yyyy\#")
") OR Me.txtRequestedDate IS NULL"
 
Back
Top