Solved

RunTime Error 3075 Missing Operator

Posted on 2014-01-15
5
1,505 Views
Last Modified: 2014-01-16
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
Comment
Question by:rsburge
5 Comments
 
LVL 24

Expert Comment

by:chaau
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 

Author Comment

by:rsburge
Comment Utility
Thank you.  I will test this as soon as the evening processes are finished running.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
try this


strSQL = "UPDATE [tblLenders] SET Notes =" & chr(34) &  ST & Chr(34) & " WHERE ID=" & ln
CurrentDb.Execute strSQL, dbFailOnError
0
 

Author Comment

by:rsburge
Comment Utility
Thank you Experts!

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

Thanks!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now