Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

RunTime Error 3075 Missing Operator

Posted on 2014-01-15
5
Medium Priority
?
1,961 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 25

Expert Comment

by:chaau
ID: 39784333
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 49

Expert Comment

by:Dale Fye
ID: 39784350
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
ID: 39784351
Thank you.  I will test this as soon as the evening processes are finished running.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39785276
try this


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

Author Comment

by:rsburge
ID: 39786627
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

773 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