Don't think my brain's turned on this morning yet.. I'm trying to add a functionality that calculates the mileage for each employee of a homecare company. This works fine. The rule is though that the mileage from the Carer's home to the first client of the day doesn't get paid for, so I'd like to set it as zero. All I want to do is loop through each day of a set of bookings, per carer, and set each carer's first booking of the day as zero. What i've done below doesn't work as from time to time it updates a booking which is not the first booking of the carer's day?
sqltxt = "select carerid from tblcarer"
Set qryCarer = db.OpenRecordset(sqltxt, dbOpenSnapshot)
If Not qryCarer.EOF Then
Do While Not qryCarer.EOF
sqltxt = "SELECT clientid, carerid, bookingid, startdatetime, Length FROM tmpMileageBookings where carerid = " & qryCarer("carerid") & " order by startdatetime ASC"
Set qrycheckbookings = db.OpenRecordset(sqltxt, dbOpenSnapshot)
If Not qrycheckbookings.EOF Then
Do While Not qrycheckbookings.EOF
sqltxt = "SELECT clientid, bookingid, startdatetime FROM tmpMileageBookings where carerid = " & qrycheckbookings("Carerid") & " AND startdatetime >= #" & Format(qrycheckbookings("startdatetime"), "mm/dd/yyyy") & " 00:00:00# and startdatetime <= #" & Format(qrycheckbookings("startdatetime"), "mm/dd/yyyy") & " 23:59:59# order by startdatetime asc"
Set qryfirstbooking = db.OpenRecordset(sqltxt, dbOpenSnapshot)
If Not qryfirstbooking.EOF Then
sqltxt = "Update tblbooking set mileage = 0 where bookingid = " & qryfirstbooking("bookingid")
db.Execute sqltxt, dbSeeChanges
Any ideas? Spending way too much time on this than I should!
SELECT TOP 1 bookingid
GROUP BY carerid, startdatetime, bookingid
HAVING (carerid = Number) AND (startdatetime >= DayDate) AND (startdatetime < (DayDate + 1))
ORDER BY startdatetime
I've created an extra date variable to get round the formatting issues.