Access 2013 SQL and Apostrophes/Commas ERROR 3075

Question

Access 2013 SQL and Apostrophes/Commas ERROR 3075

Knicks (TechnicalUser)

(OP)

15 Feb 19 19:41

I have inherited a piece of code that tests for duplicates and the code is triggering error 3075 when encountering apostrophes and/or commas.

Here is the SQL code, any help and or direction. I tried isolating the error number but I don't think that is the answer, I would prefer the form entry be capable of handling the difficult characters. Would a variable set to the form entry remove the problem? This is happening in a BEFORE UPDATE event of the txtLegalName from the form, essentially this is just supposed to test for duplicates but a name like P'ing, LLC is tripping it up. Since these are legal names they would like to be able to continue using those types of characters

Set rstEntities = CurrentDb.OpenRecordset("SELECT EntityID " & _
"FROM tblEntity " & _
"WHERE LegalName = '" & txtLegalName & "' AND " & _
"EntityID <> " & Nz(Me.EntityID, 0), , dbReadOnly)

If rstEntities.RecordCount > 0 Then
MsgBox "This entity already exists. Enter another name or press Esc to undo your entry.", vbExclamation, "Oops!"
End If

Andrzejek (Programmer) · Answer 1 · 2019-02-15 19:54:27

Consider this:

CODE

Dim strSQL As String

strSQL = "SELECT EntityID " & _
 "FROM tblEntity " & _
 "WHERE LegalName = '" & Replace(txtLegalName, "'", "''") & "' AND " & _
 "EntityID <> " & Nz(Me.EntityID, 0)

'Debug.Print strSQL

Set rstEntities = CurrentDb.OpenRecordset(strSQL, , dbReadOnly)

If rstEntities.RecordCount > 0 Then
    MsgBox "This entity already exists. Enter another name or press Esc to undo your entry.", vbExclamation, "Oops!"
End If

In short, replace single quote with 2 single quotes.

---- Andy

There is a great need for a sarcasm font.

Knicks (TechnicalUser) · Answer 2 · 2019-02-15 20:04:11

Just ran some testing and that appears to have fixed it. Still finds the duplicates while also allowing the P'ing, LLC in.

Thanx for the quick and quality response

Andrzejek (Programmer) · Answer 3 · 2019-02-15 20:11:44

I use this Replace() trick anywhere in my SQLs (Selects, Updates, etc.) where there is a possibility of a single quote

---- Andy

There is a great need for a sarcasm font.

Knicks (TechnicalUser) · Answer 4 · 2019-02-15 20:27:46

I assume "" quotes in a name would mess it up as MS Access needs to use one of these text qualifiers.....but the chances of a double quote in a name are extremely low

Andrzejek (Programmer) · Answer 5 · 2019-02-15 20:35:48

I don't think double quotes " will cause the same issues.
Single quote is the start of a comment in VBA, that's why it creates a problem.

Consider this sample:

CODE

Dim strSQL As String
Dim txtLegalName As String

txtLegalName = "This is my test " & Chr(34) & " The end"

strSQL = "SELECT EntityID " & _
 "FROM tblEntity " & _
 "WHERE LegalName = '" & Replace(txtLegalName, "'", "''") & "' AND " & _
 "EntityID <> " & 0

strSQL has this as the outcome:
SELECT EntityID FROM tblEntity WHERE LegalName = 'This is my test " The end' AND EntityID <> 0
so the " is as any other character (other than ')

---- Andy

There is a great need for a sarcasm font.

Andrzejek (Programmer) · Answer 6 · 2019-02-15 20:57:29

Quote (Knicks )
test for duplicates

You may want to employ a different approach: set UNIQUE Constraint on LegalName field in your tblEntity table and - if violated - catch the error and react accordingly.

And on dealing with single quotes - you can change your approach and use parameterized queries instead. That would also take care of a possibility of SQL injection in your application.

---- Andy

There is a great need for a sarcasm font.

Come Join Us!

Posting Guidelines

Access 2013 SQL and Apostrophes/Commas ERROR 3075