Apostrophe problem (1 Viewer)

[kkeegler]

A button opens a new data entry form and works for all my clients except when the name contains an apostrophe like O'Brien

The code follows:

Private Sub cmdNewSubscriber_Click()
On Error GoTo Err_cmdNewSubscriber_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNewSubjectEntry"


stLinkCriteria = "[ClientNum]=" & "'" & Me![ClientCompanyName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdNewSubscriber_Click:
Exit Sub

Err_cmdNewSubscriber_Click:
MsgBox Err.Description
Resume Exit_cmdNewSubscriber_Click

End Sub

Any suggestions

 

[The_Doc_Man]

If I read this correctly, your problem is that names containing an apostrophe interfere with your quoted apostrophes.

Basically, when you are going to allow apostrophe as data, you cannot use it in your syntax. This means you must use the double quotes even though they will seem to get in the way.

stLinkCriteria = "[ClientNum]=""" & Me![ClientCompanyName] & """"

That is three double-quotes to the immediate right of the equals sign and four double-quotes as the end of the string.

 

[kkeegler]

PERFECT!!!
Fast and accurate - it doesn't get any better than that.

Thanks

 

[David R]

Actually...

Rather than counting quotation marks, I usually use the Chr(34) to represent the single quote I need. Somehow Access interprets this differently and names like O'Brien work fine.

For example:

stLinkCriteria = "[ClientNum]=" & Chr(34) & Me![ClientCompanyName] & Chr(34)

results in:

"[ClientNum]='O'Brien'"

in a way Access doesn't flip out over.

Regards,
David R

 

[jaydwest]

Apostrophe's in criteria

The easiest way to resolve this problem is to declare a string variable for Double Quotes. For example

Dim DQ as string

Then define it as

DQ = Chr$(34)

We declare DQ as a global variable so it can be used anywhere in the database.

Then simply surround your field with DQ's as shown below.

strCriteria = DQ & strCriteria & DQ

This eliminates the need for parsing strCriteria to find apostrophes.

Good Luck