Solved

RunTime Error 3075 Missing Operator

Posted on 2014-01-15
5
1,609 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
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 47

Expert Comment

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 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