Error 3075 When breaking down long text for update query

Question

The Description field is a long text field with over 255 characters. so I'm trying to break it down before I update it. My error reads:

Run-Time error '3075 Syntax error (missing Operator) in query expression "titlename'Set Description = '([prm_val1] & [prm_val2].....[prm_val7])".

Here's my code:

l = Len(Me.Description)
If l Mod 255 = 0 Then   'For length exactly a multiple of 255 (255, 510, 765...)
    n = l / 255
Else
    n = Int(l / 255) + 1
End If

sp = "[prm_val1]"
If n > 1 Then 'If >255 chars tap on concatenated parameters as needed
     For p = 2 To n
         sp = sp & " & [prm_val" & p & "]"
     Next p
 End If

'UpdateDescription = "UPDATE AllProjects " & _
'"Set Description='" & sp & "'" & _
'" WHERE ID =" & Me.ID
'CurrentDb.Execute UpdateDescription

 'For p = 1 To n   'Add each 255 char piece as the parameters
     'qdf.Parameters("prm_val" & p) = Mid(varValue, (p - 1) * 255 + 1, 255)
 'Next p
 'qdf.Execute



    DoCmd.SetWarnings False
    strSQL = "UPDATE AllProjects " & _
     "Set Title='" & Me.Title & "'" & _
     "Set Description='(" & sp & ")'" & _
     ",Department='" & Me.Department & "'" & _
     ",Priority='" & Me.Priority & "'" & _
     ",Status='" & Me.Status & "'" & _
     ",[Create Date]='" & Me.CreateDate & "'" & _
     ",[% Complete]='" & Me.PerComplete & "'" & _
     ",[File Location]='" & Me.FileLocation & "'" & _
     ",[Update Notes]='" & Me.UpdateNotes & "'" & _
     ",Leader='" & Me.Leader & "'" & _
     ",[Target Date]='" & Me.TargetDate & "'" & _
     ",[Complete Date]='" & Me.CompleteDate & "'" & _
     ",Category='" & Me.Category & "'" & _
     ",Feedback='" & Me.Feedback & "'" & _
     ",[File Location 2]='" & Me.FileLocation2 & "'" & _
     ",[File Location 3]='" & Me.FileLocation3 & "'" & _
     ",[Strategic Initiatives]='" & Me.StrategicInitiatives & "'" & _
     " WHERE ID =" & Me.ID
     CurrentDb.Execute strSQL

     For p = 1 To n   'Add each 255 char piece as the parameters
             strSQL.Parameters("prm_val" & p) = Mid(varValue, (p - 1) * 255 + 1, 255)
         Next p
    DoCmd.SetWarnings True

Given that you can only assign a single value to that field, what is the reason for trying to break it down first? — Dan Bracuk, Dec 23 '15 at 13:09

score 0 · Answer 1 · answered Dec 23 '15 at 13:45

This code is called after the execute, and does not make any sense:

For p = 1 To n   'Add each 255 char piece as the parameters
    strSQL.Parameters("prm_val" & p) = Mid(varValue, (p - 1) * 255 + 1, 255)
Next p

as strSQL doesn't take parameters.

Adjust this code and move it inte the code before calling execute.

Or use the query you have commented out.

Error 3075 When breaking down long text for update query

1 Answers1