×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Access 2013 SQL and Apostrophes/Commas ERROR 3075

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

RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075

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



---- Andy

There is a great need for a sarcasm font.

RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075

(OP)
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

RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075

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.

RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075

(OP)
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

RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075

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.

RE: Access 2013 SQL and Apostrophes/Commas ERROR 3075

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close