Apostrophe in Memo Field causing Synax Error

[MHamid]

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".

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:

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

 

[james_lankford]

maybe

 '" & 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

 

[Micron]

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.

 

[MHamid]

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

 

[james_lankford]

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