using update sql on a memo field

i'm trying to use an update sql command on a memo field in access, but unfortunately, it's not working. i'm getting a "Syntax error (missing operator) in query expression" run-time error. i'm newish to coding in access, and am not sure what this means.

how can i get my 255+ length string into my field?

here is the relevant code:


Option Explicit

Sub play()

Dim exlFile As excel.Application
Dim MyFile As Variant
Dim pdqID As Integer
Dim MyConn As New ADODB.Connection
Dim MyRecSetReturn As New ADODB.Recordset
Dim MyRecSetReturnMemoF9 As New ADODB.Recordset
Dim MyRecSet As New ADODB.Recordset
Dim N As Integer
Dim myRow As Integer
Dim myRow2 As Integer
Dim myStop As Integer
Dim strField As String
Dim strField2 As String
Dim strField3 As String
Dim strField4 As String
Dim strField5 As String
Dim strFieldMemo As String
Dim strField2Memo As String
Dim strField3Memo As String
Dim strField4Memo As String

MyRecSetReturn.Open ("SELECT * FROM [Imports] WHERE [F41] = " & pdqID & ""), MyConn

MyRecSetReturnMemoF9.Open ("SELECT mid(F9,1,255) as part1, mid(F9,256,255) as part2, mid(F9,511,255) as part3, mid(F9,766,255) as part4 FROM [Imports] WHERE [f41] = " & pdqID & ""), MyConn

myStop = 40 'updating the jobs table here
For myRow = 1 To myStop
If Not (IsNull(MyRecSetReturn.Fields("F" & myRow & ""))) Then
strField = MyRecSetReturn.Fields.Item("F" & myRow & "").Value
strFieldMemo = MyRecSetReturnMemoF9.Fields.Item("part1").Value
strField2Memo = MyRecSetReturnMemoF9.Fields.Item("part2").Value
strField3Memo = MyRecSetReturnMemoF9.Fields.Item("part3").Value
strField4Memo = MyRecSetReturnMemoF9.Fields.Item("part4").Value
Select Case myRow
'cases 1-8
Case 9
Set MyRecSet = MyConn.Execute("UPDATE [Jobs] SET [Jobs].[Why] = '" & strFieldMemo & strField2Memo & strField3Memo & strField4Memo & "' WHERE [Jobs].[ID] = " & pdqID & "")
'and so on

I think you have to use the CAST function to cast the field from one datatype to the another.

For example:
ORDER BY CAST([FieldName] as varchar(255))

hmm, i can't even find those functions/commands in the help file. what do those do?

MyConn.Execute "UPDATE [Jobs] SET [Jobs].[Why] = '" & strFieldMemo & strField2Memo & strField3Memo & strField4Memo & "' WHERE [Jobs].[ID] = " & pdqID & ""

Since an execute doesn't need to (and shouldn't) return a recordset, use the connection.Execute method as per above.

thanks for the help. unfortunately, i'm still getting the same error. do you have any other suggestions?

ooooh, it works if i take out the slashes that are in the text string. / / / / / / /

is there a way for me to incorporate these in there without causing an error?