Syntax error - Apostrophe

[Guest]

I have a command button that opens a record based on what company is selected
from a combo box.

A new company that I have has an apostrophe in the name. When clicking on
the command button after selecting from combo box I get a syntax error.

I tried doubling up the apostrophe just like you have to do on a label for
the "&" sign. That didn't work.

Can someone help me with this? Here is what I have in the "On Click" Event.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmBlueBookCompanies"

stLinkCriteria = "[CompanyName]=" & "'" & Me![BBCoName] & "'"
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria

 

[John Spencer]

Good posting. The relevant information seems to be all there.
Use quotes as the delimiter

stLinkCriteria = "[CompanyName]=" & Chr(34) & Me![BBCoName] & Chr(34)

or
stLinkCriteria = "[CompanyName]=""" & Me![BBCoName] & """"

Or use the replace function (Access 2K or later) to double the apostrophe in
BBCCoName

stLinkCriteria = "[CompanyName]='" & Replace(Me![BBCoName], "'","''") &
"'"

Exagerated (extra spaces addeed - remove them) for clarity the replace is

Replace(Me![BBCoName], " ' "," ' ' ")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

The first one worked great...thank you very much!
--
Thank, Lori

Good posting. The relevant information seems to be all there.
Use quotes as the delimiter

stLinkCriteria = "[CompanyName]=" & Chr(34) & Me![BBCoName] & Chr(34)

or
stLinkCriteria = "[CompanyName]=""" & Me![BBCoName] & """"

Or use the replace function (Access 2K or later) to double the apostrophe in
BBCCoName

stLinkCriteria = "[CompanyName]='" & Replace(Me![BBCoName], "'","''") &
"'"

Exagerated (extra spaces addeed - remove them) for clarity the replace is

Replace(Me![BBCoName], " ' "," ' ' ")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a command button that opens a record based on what company is
selected
from a combo box.

A new company that I have has an apostrophe in the name. When clicking on
the command button after selecting from combo box I get a syntax error.

I tried doubling up the apostrophe just like you have to do on a label for
the "&" sign. That didn't work.

Can someone help me with this? Here is what I have in the "On Click"
Event.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmBlueBookCompanies"

stLinkCriteria = "[CompanyName]=" & "'" & Me![BBCoName] & "'"
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria