Access 2013 SQL and Apostrophes/Commas ERROR 3075 1

[Knicks]

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]

Consider this:

Dim strSQL As String

strSQL = "SELECT EntityID " & _
 "FROM tblEntity " & _
 "WHERE LegalName = '" & [blue]Replace([/blue]txtLegalName[blue], "'", "''")[/blue] & "' AND " & _
 "EntityID <> " & Nz(Me.EntityID, 0)
[green]
'Debug.Print strSQL[/green]

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]

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]

I use this [tt]Replace()[/tt] 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]

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]

I don't think double quotes [tt]"[/tt] 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:

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:
[tt] SELECT EntityID FROM tblEntity WHERE LegalName = 'This is my test " The end' AND EntityID <> 0 [/tt]
so the " is as any other character (other than ')


---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

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.