Updating Records Where Driver Not Already Assigned

[DMT Dave]

Hi Guy's I am having trouble with this one



I am trying to find which driver name is not assigned for work on a Thursday, the issue updating warehouse cover to go to "Del To" Field (sSQL2)

in Immediate window, all of the criteria match so i am trying to update but coming up with Runtime Error 3075 which i believe is data type ?

Can you guy's see anything wrong with sSQL2 considering, this is the debug window ?

much appreciated

Debug.Print dtThur & " ; " & sThur & vbCrLf & strTask & " ; " & sDriver
Imm Window
14/03/2024 ; Thursday
Warehouse Cover ; Drivers Name removed on Forum (already assigned with del no >0)

Dim dtMon As Date, dtFri As Date, dtDayMon As Date, dtDayFri As Date, dtNow As Date, dtThur As Date
Dim sSQL As String, sSQL2 As String, sMon As String, sThur As String, sFri As String, strMonTask As String, strTask As String
Dim sDriver As String
Dim rs As DAO.Recordset




dtNow = Format(Now(), "dd/mm/yyyy")


If Weekday(Now()) = 6 Then
    dtFri = 13 - Weekday(Date) + Date
    dtMon = 9 - Weekday(Date) + Date
    dtThur = 12 - Weekday(Date) + Date
Else
    dtFri = 6 - Weekday(Date) + Date
    dtMon = 2 - Weekday(Date) + Date
    dtThur = 5 - Weekday(Date) + Date
End If


sThur = WeekdayName(Weekday(dtThur, 0), False, 0)


If Not IsNull(DLookup("DelDate", "tblRoutes", "[DelDate] = #" & dtThur & "#")) Then
    Set rs = CurrentDb.OpenRecordset("Select * From tblRoutes " _
            & "WHERE DelDate = #" & dtThur & "#")
        Do Until rs.EOF
            sDriver = rs.Fields("Driver")
            rs.MoveNext
        Loop
End If


sSQL = "INSERT INTO tblRoutes ( Driver ) " _
        & "SELECT tblStaff.Name " _
        & "From tblStaff " _
        & "WHERE (((tblStaff.Name) Not In ('" & sDriver & "') And ((tblStaff.DelsMail) = Yes)));"

'ADDS ALL STAFF APART FROM (sDriver Correct, already assigned)


DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
DoCmd.SetWarnings True


strTask = "Warehouse Cover"


sSQL2 = "UPDATE tblRoutes SET tblRoutes.DelDate = #" & Format(dtThur, "mm/dd/yyyy") & "#, tblRoutes.DayName = '" & sThur & "', tblRoutes.DelTo = '" & strTask & "' " _
        & "WHERE (((tblRoutes.DelNo)=0) AND ((tblRoutes.DelDate) = #" & dtThur & "#) AND ((tblRoutes.Driver) <> & '" & sDriver & "'));"


Debug.Print dtThur & " ; " & sThur & vbCrLf & strTask & " ; " & sDriver




DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL2
DoCmd.SetWarnings True


Forms!frmPlanning!frmPlanningDS.Requery

[Welshgasman]

You are not concatenating sDriver, but overwriting on each record?
So you will always get the last one?

[DMT Dave]

Ahh WGM, ok so you mean by concatenating sDriver = sDriver & rs.Fields etc... ?

[DMT Dave]

I will put SQL2 into a select query to try and get the results and change back to an update query

that's usually not bad practice

[Welshgasman]

Ahh WGM, ok so you mean by concatenating sDriver = sDriver & rs.Fields etc... ?

Yes, with , to separate. I am saying this as you are using IN in your sql.

[Gicu]

You need to move the INSERT and UPDATE statements inside the Do Until rs.EOF loop...

[DMT Dave]

Hi Vlad, thank you, moved as suggested and as I understand we are looping the INSERT AND UPDATE, now had a tidy up

do i still need to run SQL after loop ?

also still having a problem with sSQL2

as below

Tidy code

sThur = WeekdayName(Weekday(dtThur, 0), False, 0)

strTask = "Warehouse Cover"


If Not IsNull(DLookup("DelDate", "tblRoutes", "[DelDate] = #" & dtThur & "#")) Then
    Set rs = CurrentDb.OpenRecordset("Select * From tblRoutes " _
        & "WHERE DelDate = #" & dtThur & "#")
    
    Do Until rs.EOF
        sDriver = rs.Fields("Driver")
            
    sSQL = "INSERT INTO tblRoutes ( Driver ) " _
        & "SELECT tblStaff.Name " _
        & "From tblStaff " _
        & "WHERE (((tblStaff.Name) Not In ('" & sDriver & "') And ((tblStaff.DelsMail) = Yes)));"


    sSQL2 = "UPDATE tblRoutes SET tblRoutes.DelDate = #" & Format(dtThur, "mm/dd/yyyy") & "#, tblRoutes.DayName = '" & sThur & "', tblRoutes.DelTo = '" & strTask & "' " _
        & "WHERE (((tblRoutes.DelNo)=0) AND ((tblRoutes.DelDate) = #" & dtThur & "#) AND ((tblRoutes.Driver) <> & '" & sDriver & "'));"


        rs.MoveNext
    Loop
End If

Debug.Print sSQL2


DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
    DoCmd.RunSQL sSQL2
DoCmd.SetWarnings True


Forms!frmPlanning!frmPlanningDS.Requery

The only other part that i can change is something i see on this forum regular, formatting dtThur to mm/dd/yyyy (USA) instead of UK dd/mm/yyyy or not formatting at all

I have just had other issues ona customer update file where records didn't out put all of a sudden, changed to formatting to mm/dd/yyyy and worked

I have just noticed on line 1 of sSQL2, i have FOrmat(dtThur,mm/dd/yyyy") then on line 2, i haven't formatted

do you think that is data reading issue ?

thanks again

[Welshgasman]

How many times do you need to be told?
Debug.print your sql strings.
The fact that that error message shows & tells me your concatenation is off. Also shouldn't the date be in mm/dd/yyyy format?

[DMT Dave]

Hi WGM, yes i noticed i didn't format as mm/dd/yyyy stated that in my last post, going to change that now

I have debug.print sSQL2 to try and see where the issue is, but going to log back in and format dates to USA format, i have noticed that sometimes dates work ok with dd/mm then in some areas doesn't work

[DMT Dave]

Getting Missing Operator so going to check delimiters in sSQL2 (even after formatting to mm/dd/yyyy)

I think i have lost my self in trying lol

all i was trying to do was to find driver names in staff where DelsMail = true

check tblRoutes to see if no one is assigned for Thursday

assign whoever is NOT assigned to Thursday to warehouse tasks

but lost myself on route with it

[DMT Dave]

This is my Debug.Print Window

UPDATE tblRoutes SET tblRoutes.DelDate = #03/14/2024#, tblRoutes.DayName = 'Thursday', tblRoutes.DelTo = 'Warehouse Cover' WHERE (((tblRoutes.DelNo)=0) AND ((tblRoutes.DelDate) = #03/14/2024#) AND ((tblRoutes.Driver) <> & 'Joe Bloggs'));

Removed correct driver name

[Micron]

why do you have an ampersand between <> and Joe?

[Welshgasman]

why do you have an ampersand between <> and Joe?

Yes, that is one of the issues. You should not see those.

[Welshgasman]

Well access knows you cannot have 14 months, so if you use 14/03/2024, it knows it is 14th March. However, 09/03/2024 would be taken as 3rd September.
Use yyyy-mm-dd if you cannot remember to use the correct format.

[DMT Dave]

Ahh ok WGM, thank you, never tried using yyyy first, always used dd/mm/yyyy or mm/dd/yyyy

yes i did see 03/14/2024 in my immediate window but took it that we formatted US format