Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

RunTime Error 3075 Missing Operator

Posted on 2014-01-15
5
Medium Priority
?
1,894 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 48

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

Technology Partners: 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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

715 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