Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access 2013 SQL and Apostrophes/Commas ERROR 3075 1

Status
Not open for further replies.
Knicks

Knicks

Technical User
Joined
Apr 1, 2002
Messages
384
Location
US
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
 
Sort by date Sort by votes
Consider this:

Code: 
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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:

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:
[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.
 
Upvote 0 Downvote
Knicks said:
test for duplicates
Click to expand...

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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

C
  • Locked
  • Question Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
610
OzFoxy
OzFoxy
combo
  • Locked
  • Question Question
SQL data types in CREATE/ALTER TABLE queries
Replies
2
Views
3K
combo
combo
datajockey123
  • Locked
  • Question Question
Note / Information box with date and time stamp for each entery
Replies
2
Views
997
dhookom
dhookom
Swi
  • Locked
  • Question Question
Jet Sql Statement - Select Distinct Multiple Columns
Replies
4
Views
1K
Swi
Swi
lameid
  • Locked
  • Question Question
SELECT FROM Table in Another Access File
Replies
2
Views
4K
lameid
lameid

Part and Inventory Search

Sponsor

Back
Top