Access 2013 SQL and Apostrophes/Commas ERROR 3075
Access 2013 SQL and Apostrophes/Commas ERROR 3075
(OP)
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
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
RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075
CODE
In short, replace single quote with 2 single quotes.
---- Andy
There is a great need for a sarcasm font.
RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075
Thanx for the quick and quality response
RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075
---- Andy
There is a great need for a sarcasm font.
RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075
RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075
Single quote is the start of a comment in VBA, that's why it creates a problem.
Consider this sample:
CODE
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.
RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075
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.