Apostrophe in Memo Field causing Synax Error

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I am getting the following error message "Syntax error (missing operator) in query expression". My search in google turned out that the cause of this error in my situation was an apostrophe being used. I have a bound data entry form with a field called Notes where the user can add as much as text as possible. However, it was brought to my attention that when they tried to write a comments with words that have an apostrophe (can't or let's) this error popped up.

I tried the below fix to allow text to be entered with an apostrophe, but then I am getting a "Error 94: Invalid use of Null".

Code:
CurrentDb.Execute "INSERT INTO QAMaster([EnteredBy], [DateEntered], [CycleMonth], [CycleYear], [ReportType], [DateReviewed], [ReviewerType], [Reviewer], [ReviewerReportArea], [MainSection], [TopicSection], [OwnershipIndividual], [OwnershipTeam], [Count], [Priority], [ApprovedBy], [L1], [L2], [L3], [Other], [RepeatAsk], [Exception], [Notes], [Outliers], [AdditionalOutlier], [Hyperlink], [PageNo])" & _
    "VALUES ('" & Me.EnteredBy & "',  '" & Me.DateEntered & "', '" & Me.CycleMonth & "',  '" & Me.CycleYear & "', '" & Me.ReportType & "', '" & Me.DateReviewed & "', '" & Me.ReviewerType & "', '" & Me.Reviewer & "', '" & Me.ReviewerReportArea & "', '" & Me.MainSection & "', '" & Me.TopicSection & "', '" & Me.Individual & "', '" & Me.Team & "','" & Me.txtCount & "', '" & Me.PriorityLevel & "', '" & Me.Approved & "', '" & Me.L1 & "', '" & Me.L2 & "', '" & Me.L3 & "', '" & Me.txtOther & "', '" & Me.Repeat & "', '" & Me.Exception & "', [COLOR=#FF0000]'" & SafeSQL(Me.Notes) & "'[/COLOR], '" & Me.Outliers & "', '" & Me.txtOther2 & "', '" & Me.txtHyperlink & "', '" & Me.txtPageNo & "');", dbFailOnError

Where the code for SafeSQL is:
Code:
Public Function SafeSQL(strArg As String) As String
    SafeSQL = Replace(strArg, "'", "''")
End Function

I need this Notes field to be able to allow text to be added with apostrophes and allow it to be blank. How can I do this?


Thank you
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
maybe


Code:
 '" & Me.Exception & "', " & SafeSQL(Me.Notes) & ", '" & Me.Outliers & "', '"

Public Function SafeSQL(strArg As String) As String
    if strArg = "" then 
        SafeSQL = "NULL"
    else 
        SafeSQL = "'" & Replace(strArg, "'", "''") & "'"  
    end if
End Function
 
Upvote 0
try something along the syntax of "INSERT INTO tblMyTableName(MyfieldName) VALUES (...""" & str & """...)" where the dots just represent what comes before and after the problem field in your sql expression. In other words, """ & Me.Notes & """ and forget the function call.
I know that works if done properly. You shouldn't have to, but you could also assign the function return to a variable and use the variable instead of Me.Notes.

There is another way that I know of, but it would require repeatedly modifying the sql of a query def and I'm not a big believer of that.
 
Upvote 0
Hello,

James - I have tried a similar scenario like that, but the field will be filled with the word Null on the table and I don't want that to happen.

Micron - I tried your suggestion and it worked perfectly. This method allows for words with apostrophes and to leave the filed blank.

Thank you for your help
 
Upvote 0
Hello,

James - I have tried a similar scenario like that, but the field will be filled with the word Null on the table and I don't want that to happen.

but I also changed the insert line
I took out thesingle quotes that you had surrounding the function

you had
& "', '" & SafeSQL(Me.Notes) & "', '" &
so you're inserting 'NULL'


I changed it to
& "', " & SafeSQL(Me.Notes) & ", '"
so I'm inserting
NULL
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top