MS Access running SQL Update Error 3464

Question

So I have been searching on here but can't seem to find an answer that makes my code work. I know I'm just missing something but can't figure it out.

I am Running MS Access 2013 and trying to run the follow SQL "Update" in VBA but keep getting

runtime error 3464 : Data Type Mismatch

Function ChangeTaskRank(NewRank As Integer, PriorityLevel As Integer, UserGroup As String)

Dim db As DAO.Database
Dim tableDF As DAO.TableDef

Dim sqlString As String
Dim currentRank As Integer
Dim nextRank As Integer

Set db = CurrentDb()
Set tableDF = db.TableDefs("Task_List")

SelectTaskQueryDB PriorityLevel, UserGroup

currentRank = TempVars!tmp_Task_MaxRank

Do While (currentRank >= NewRank)

    nextRank = currentRank + 1

    db.Execute "UPDATE Task_List " & _
    "SET [Task_Rank]= " & nextRank & " " & _
    "WHERE (([Task_Rank] = " & currentRank & ") AND ([Task_Priority] = " & PriorityLevel & ") AND ([Task_UserGroup] = '" & UserGroup & "'));"


    'Update
    'SET Task_Rank = (currentRank + 1)
    'WHERE (([Task_Priority] = PriorityLevel) AND ([Task_UserGroup] = UserGroup));

    currentRank = currentRank - 1

Loop

End Function

What I am trying to do with code is run a loop that will change the Rank of a task when a new one is added (IE: you have task rank 1 to 5 and add a new task at 3, the code will change the task rank of 3,4,5 to 4,5,6.)

Database info:

Table name: Task_List
Field Name: Task_Rank (Integer)

Filters:

[Task_Rank] = currentRank (Integer)
[Task_Priority] = PriorityLevel (Integer)
[Task_UserGroup] = UserGroup (String)

Any help would be great as this is driving me nuts lol. Thanks in advance.

With debugging like this, I like to set the SQL to a String first, and then use `Debug.Print sSQL` to make sure the results make sense, then you can `db.Execute sSQL` - When you do this, what are the results? — braX, Feb 01 '18 at 13:33
need the code for SelectTaskQueryDB and the line of the error — Ctznkane525, Feb 01 '18 at 13:35
[How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) — Andre, Feb 01 '18 at 14:52
@brax When I run the Debug I get: UPDATE Task_List SET [Task_Rank]= 2 WHERE (([Task_Rank] = 1) AND ([Task_Priority] = 1) AND ([Task_UserGroup] = 'BIM Group')); Which has all the correct numbers and string in it. — RSD, Feb 01 '18 at 15:01
Is your `Task_Priority` field also integer *in the database table* ? — LocEngineer, Feb 01 '18 at 15:03
.....Well..... I thought I had set it to integer... But now that you say that, it did not save change... Thank you very much guys. Problem solved. — RSD, Feb 01 '18 at 15:08
Welcome to Stack Overflow! [You do not need to mark questions as "SOLVED" via editing the title](//meta.stackexchange.com/a/116105/295637), or [posting updates/thanks in posts](//meta.stackexchange.com/a/109959/295637). See: **[What should I do when someone answers my question?](//stackoverflow.com/help/someone-answers)** Simply marking an answer as accepted will mark this question as solved for future readers. Anything additional can be perceived as noise for future visitors. — Blue, Feb 01 '18 at 15:12

score 2 · Accepted Answer · answered Feb 01 '18 at 15:10

Consider a parameterized SQL query with a conditional IIF to avoid any looping, quote enclosures, and string concatenation.

SQL (save as a saved query)

PARAMETERS NewRankParam LONG, PriorityLevelParam LONG, UserGroupParam TEXT;
UPDATE Task_List  t
SET [Task_Rank]= IIF(t.[Task_Rank] >= NewRankParam, t.[Task_Rank] + 1, t.[Task_Rank])
WHERE ((t.[Task_Priority] = PriorityLevelParam) AND (t.[Task_UserGroup] = UserGroupParam));

VBA

Function ChangeTaskRank(NewRank As Integer, PriorityLevel As Integer, UserGroup As String)

    Dim db As DAO.Database
    Dim qdef As QueryDef

    Set db = CurrentDb
    Set qdef = db.QueryDefs("mySavedQuery")

    qdef!NewRankParam = NewRank
    qdef!PriorityLevelParam = PriorityLevel
    qdef!UserGroupParam = UserGroup

    qdef.Execute dbFailOnError

    Set db = Nothing
    Set qdef = Nothing

End Function

MS Access running SQL Update Error 3464

1 Answers1