Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    13

    Unanswered: Loop through each day

    Hi guys,

    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"

    Debug.Print (sqltxt)
    Set qryfirstbooking = db.OpenRecordset(sqltxt, dbOpenSnapshot)
    If Not qryfirstbooking.EOF Then
    qryfirstbooking.MoveFirst
    sqltxt = "Update tblbooking set mileage = 0 where bookingid = " & qryfirstbooking("bookingid")
    Debug.Print sqltxt
    db.Execute sqltxt, dbSeeChanges
    End If

    qrycheckbookings.MoveNext
    Loop
    End If

    qryCarer.MoveNext
    Loop
    End If

    Any ideas? Spending way too much time on this than I should!

  2. #2
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    Check for conflicting indexes in tmpMileageBookings and only include the bookingid column in the recordset string:

    "SELECT tmpMileageBookings.bookingid FROM tmpMileageBookings WHERE (((tmpMileageBookings.carerid) = " & qrycheckbookings("Carerid") & ") AND ((tmpMileageBookings.startdatetime)>=#" & Format(qrycheckbookings("startdatetime"), "mm/dd/yyyy") & " 00:00:00#)) AND ((tmpMileageBookings.startdatetime)<= #" & Format(qrycheckbookings("startdatetime"), "mm/dd/yyyy") & " 23:59:59#))) ORDER BY startdatetime;"

  3. #3
    Join Date
    Jan 2010
    Posts
    13
    Quote Originally Posted by MyNewFlavour View Post
    Check for conflicting indexes in tmpMileageBookings and only include the bookingid column in the recordset string:

    "SELECT tmpMileageBookings.bookingid FROM tmpMileageBookings WHERE (((tmpMileageBookings.carerid) = " & qrycheckbookings("Carerid") & ") AND ((tmpMileageBookings.startdatetime)>=#" & Format(qrycheckbookings("startdatetime"), "mm/dd/yyyy") & " 00:00:00#)) AND ((tmpMileageBookings.startdatetime)<= #" & Format(qrycheckbookings("startdatetime"), "mm/dd/yyyy") & " 23:59:59#))) ORDER BY startdatetime;"
    Thanks for your response, tried it but still no luck

  4. #4
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    I dummied this in SQL Server and Access:

    DayDate = Date(DayDate)

    SELECT TOP 1 bookingid
    FROM tmpMileageBookings
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •