• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2156
  • Last Modified:

RunTime Error 3075 Missing Operator

Hello Experts -

I am receiving error 3075, missing operator, when running the code below in an access database.

It is failing in this section when the sql is executed...
'-----update lender table notes field
strSQL = "UPDATE [tblLenders] SET Notes ='" & ST & "' WHERE ID=" & ln & ""
CurrentDb.Execute strSQL, dbFailOnError

An example of the variable data is below.

ln = 51
ST =
01/15/14 - Password changed from SunT0025
08/15/13 - adjustments updated
06/17/13 - Key products & adj updated/ckd for other changes completed by MB
05/14/13 - full updated completed by MB
04/02/12 - password changed from SunT0002 to SunT0003
03/15/12 - Checked for guideline changes
04/03/11 - guidelines and adjustments updated
03/30/11 - adjustments updated
03/06/11 - updated SRP's and adjustments
08/06/10 - Agency Plus and IO products updated


Public Sub UpdatePW()
'update password field with new password
ST = newPW
strSQL = "UPDATE [tblLenders] SET Password ='" & ST & "' WHERE ID=" & ln & ""
strSQL = strSQL
CurrentDb.Execute strSQL, dbFailOnError
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'get existing notes and set the string for the old password
fillDT = Format(Date, "mm/dd/yy")
ST = fillDT & " - Password changed from " & oldPW
rng = Nz(DLookup("[Notes]", "[tblLenders]", "[ID]= " & ln & ""))
If rng = "n/a" Or rng = "" Then
     ST = ST
Else
     ST = ST & vbCrLf & rng
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'-----update lender table notes field
strSQL = "UPDATE [tblLenders] SET Notes ='" & ST & "' WHERE ID=" & ln & ""
CurrentDb.Execute strSQL, dbFailOnError
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

Open in new window

0
rsburge
Asked:
rsburge
1 Solution
 
chaauCommented:
In order to use ADO commands with a multi-line strings you need to use ADODB Command with parameters. Here is a small example (taken from the Internet):
Dim cmd As New ADODB.Command
Dim par As ADODB.Parameter
Dim strSQL As String
Dim lngID As Long

lngID = 1

strSQL = "UPDATE MyTable SET Name = ? WHERE ID = ?"

With cmd
    .ActiveConnection = cn
    
    .CommandType = adCmdText
    .CommandText = strSQL
    
    Set par = .CreateParameter(, adVarChar, adParamInput, 50, txtName.Text)
    .Parameters.Append par
    Set par = .CreateParameter(, adInteger, adParamInput, , lngID)
    .Parameters.Append par
    
    .Execute , , adCmdText And adExecuteNoRecords
End With

Open in new window

To translate this example to your case, the code will be something like (not tested, but should work):
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'-----update lender table notes field
Dim cmd As New ADODB.Command
Dim par As ADODB.Parameter
Dim strSQL As String
Dim lngID As Long

lngID = 1

strSQL = "UPDATE [tblLenders] SET Notes = ? WHERE ID = ?"

With cmd
    .ActiveConnection = cn
    
    .CommandType = adCmdText
    .CommandText = strSQL
    
    Set par = .CreateParameter(, adVarChar, adParamInput, Len(ST), ST)
    .Parameters.Append par
    Set par = .CreateParameter(, adInteger, adParamInput, , ln) 'make sure ln is an Integer, otherwise create a new Int variable
    .Parameters.Append par
    
    .Execute , , adCmdText And adExecuteNoRecords
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Open in new window

0
 
Dale FyeCommented:
Personally, I'm against the idea of appending multiple notes to the same record.  I much prefer to have a one to many relationship between my main table and a notes table.   That way, you can prevent change to previous notes, while allowing additions.

The reason your SQL was failing is that the string contains single quotes and when you try to wrap that in single quotes it creates a situation like:

'03/06/11 - updated SRP's and adjustments

which tries to interpret the section after the single quote in SRP's as some sort of variable.   In order to resolve this, you need to replace the single quotes inside the string with two consecutive single quotes.

I'm not sure why you were trying to close that SQL string with an empty string, but assuming that the data you provided is correct and that ST is a string and that the [ID] field is numeric (and so is the variable ln), then the following should work.

'-----update lender table notes field
strSQL = "UPDATE [tblLenders] SET Notes ='" & Replace(ST, "'", "''") & "' WHERE ID= " & ln
CurrentDb.Execute strSQL, dbFailOnError

Note that you could also write that as:
strSQL = "UPDATE [tblLenders] SET Notes ='" _
            & Replace(ST, chr$(39), chr$(39) & chr$(39)) & "' WHERE ID= " & ln
0
 
rsburgeAuthor Commented:
Thank you.  I will test this as soon as the evening processes are finished running.
0
 
Rey Obrero (Capricorn1)Commented:
try this


strSQL = "UPDATE [tblLenders] SET Notes =" & chr(34) &  ST & Chr(34) & " WHERE ID=" & ln
CurrentDb.Execute strSQL, dbFailOnError
0
 
rsburgeAuthor Commented:
Thank you Experts!

I tried all of the solutions.  However, capricorn1's solution was the easiest to implement in my project.

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now