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)
Code:
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