problem with single quote mark

[Debbie S.]

Hi,

I posted this once before but I still do not understand what the problem is.
I have a form into which I enter titles of articles, in a field called
"titleofarticle". In many cases, the title may contain a word or a person's
name in possessive form, such as "Bob's Ideas on Microsoft Access." Whenever
I put a single quote anywhere in an article title, and then click on a
command button that goes to another form where I can enter more information
about this article, I get an error message that says "Syntax Error (missing
operator) in query expression '[TitleofArticle]'='This is Debbie's Test
Article," the latter being a dummy name of an article. When I posted this
before someone said something about "text delimiters." I don't get it. Can
someone explain in English what this means, why it's happening, and how I can
fix it?

Thank you,
Debbie

 

[J_Goddard via AccessMonster.com]

Hi -

It means that you are using titleofarticle somewhere in SQL. Character
strings in SQL can be delimited by single ( ' ) or double ( " ) quotes. But,
an apostrophe in a string such as Debbie's... is the same character as a
single quote.

Suppose titleofarticle contains This is Debbie's Test Article , and in
your code you delimit titleofarticle with single quotes in an SQL statement,
like this: "'" & [titleofarticle] & "'".

The resulting SQL is 'This is Debbie's Test Article' but the SQL parser
sees only 'This is Debbie' as a string, and does not know what to do with the
rest, thus generating a syntax error.

The solution is to use double quotes whenever your string values may contain
apostrophes:

..."Title is: """ & [titleofarticle] & """"

Note that I need double double-quotes to represent a double-quote within a
string delimited by double quotes. (How's that for bafflegab? Sorry!!)
This might be a bit clearer:

..."Title is: " & chr(34) & [titleofarticle] & chr(34)

Chr(34) is a built-in function to represent characters using their character
codes.

Hope you can see what I mean through all the mud!!

John

Hi,

I posted this once before but I still do not understand what the problem is.
I have a form into which I enter titles of articles, in a field called
"titleofarticle". In many cases, the title may contain a word or a person's
name in possessive form, such as "Bob's Ideas on Microsoft Access." Whenever
I put a single quote anywhere in an article title, and then click on a
command button that goes to another form where I can enter more information
about this article, I get an error message that says "Syntax Error (missing
operator) in query expression '[TitleofArticle]'='This is Debbie's Test
Article," the latter being a dummy name of an article. When I posted this
before someone said something about "text delimiters." I don't get it. Can
someone explain in English what this means, why it's happening, and how I can
fix it?

Thank you,
Debbie

 

[mscertified]

It depends on what is being done with the contents of the field.
In SQL, you cannot code a character string with a single quote inside it,
since a single quote is used to delimit the string. You need to double up any
inside single quote, which you can do as follows:
field = replace(field,"'","''")

-Dorian

 

[Debbie S.]

Right now the code looks like this:
stLinkCriteria = "[TitleOfArticle]=" & "'" & Me![TitleOfArticle] & "'"
It is code for a command button from the main article title form to another
form, filtered by the title of the article.

As you can see there are single quotes. I tried changing the inside single
quotes to double quotes, so it looked like this:

stLinkCriteria = "[TitleOfArticle]=" & """ & Me![TitleOfArticle] & """"

Two things happened. First, I still got the same syntax error. Second, when
I reopened the code, there were five double quotes at the end where I had
only put four.

What am I doing wrong? Also, what do I do when I have article titles that
may require quotation marks?

Thanks.
Debbie

It depends on what is being done with the contents of the field.
In SQL, you cannot code a character string with a single quote inside it,
since a single quote is used to delimit the string. You need to double up any
inside single quote, which you can do as follows:
field = replace(field,"'","''")

-Dorian

Hi,

I posted this once before but I still do not understand what the problem is.
I have a form into which I enter titles of articles, in a field called
"titleofarticle". In many cases, the title may contain a word or a person's
name in possessive form, such as "Bob's Ideas on Microsoft Access." Whenever
I put a single quote anywhere in an article title, and then click on a
command button that goes to another form where I can enter more information
about this article, I get an error message that says "Syntax Error (missing
operator) in query expression '[TitleofArticle]'='This is Debbie's Test
Article," the latter being a dummy name of an article. When I posted this
before someone said something about "text delimiters." I don't get it. Can
someone explain in English what this means, why it's happening, and how I can
fix it?

Thank you,
Debbie

 

[J_Goddard via AccessMonster.com]

Hi -

Anywhere you were using [titleofarticle] as part of an SQL string would
probably have to be fixed. Using [titleofarticle] to update recordset (e.g.
rst.title = [titleofarticle] ) would work fine as they are.

John

Thank you! It makes more sense, with one caveat, which is I am not too
familiar with SQL. If I were to try to fix this, where should I start? Will I
have to change this in multiple places or just one place?

Thank you
Debbie

Hi -

[quoted text clipped - 46 lines]

 

[Chris]

Try:
= "[TitleOfArticle] = """ & Me![TitleOfArticle] & """"

Right now the code looks like this:
stLinkCriteria = "[TitleOfArticle]=" & "'" & Me![TitleOfArticle] & "'"
It is code for a command button from the main article title form to another
form, filtered by the title of the article.

As you can see there are single quotes. I tried changing the inside single
quotes to double quotes, so it looked like this:

stLinkCriteria = "[TitleOfArticle]=" & """ & Me![TitleOfArticle] & """"

Two things happened. First, I still got the same syntax error. Second, when
I reopened the code, there were five double quotes at the end where I had
only put four.

What am I doing wrong? Also, what do I do when I have article titles that
may require quotation marks?

Thanks.
Debbie

It depends on what is being done with the contents of the field.
In SQL, you cannot code a character string with a single quote inside it,
since a single quote is used to delimit the string. You need to double up any
inside single quote, which you can do as follows:
field = replace(field,"'","''")

-Dorian

Hi,

I posted this once before but I still do not understand what the problem is.
I have a form into which I enter titles of articles, in a field called
"titleofarticle". In many cases, the title may contain a word or a person's
name in possessive form, such as "Bob's Ideas on Microsoft Access." Whenever
I put a single quote anywhere in an article title, and then click on a
command button that goes to another form where I can enter more information
about this article, I get an error message that says "Syntax Error (missing
operator) in query expression '[TitleofArticle]'='This is Debbie's Test
Article," the latter being a dummy name of an article. When I posted this
before someone said something about "text delimiters." I don't get it. Can
someone explain in English what this means, why it's happening, and how I can
fix it?

Thank you,
Debbie

 

[Debbie S.]

It works!!! Thank you so much!!!
Debbie

Try:
= "[TitleOfArticle] = """ & Me![TitleOfArticle] & """"

Right now the code looks like this:
stLinkCriteria = "[TitleOfArticle]=" & "'" & Me![TitleOfArticle] & "'"
It is code for a command button from the main article title form to another
form, filtered by the title of the article.

As you can see there are single quotes. I tried changing the inside single
quotes to double quotes, so it looked like this:

stLinkCriteria = "[TitleOfArticle]=" & """ & Me![TitleOfArticle] & """"

Two things happened. First, I still got the same syntax error. Second, when
I reopened the code, there were five double quotes at the end where I had
only put four.

What am I doing wrong? Also, what do I do when I have article titles that
may require quotation marks?

Thanks.
Debbie

It depends on what is being done with the contents of the field.
In SQL, you cannot code a character string with a single quote inside it,
since a single quote is used to delimit the string. You need to double up any
inside single quote, which you can do as follows:
field = replace(field,"'","''")

-Dorian

:

Hi,

I posted this once before but I still do not understand what the problem is.
I have a form into which I enter titles of articles, in a field called
"titleofarticle". In many cases, the title may contain a word or a person's
name in possessive form, such as "Bob's Ideas on Microsoft Access." Whenever
I put a single quote anywhere in an article title, and then click on a
command button that goes to another form where I can enter more information
about this article, I get an error message that says "Syntax Error (missing
operator) in query expression '[TitleofArticle]'='This is Debbie's Test
Article," the latter being a dummy name of an article. When I posted this
before someone said something about "text delimiters." I don't get it. Can
someone explain in English what this means, why it's happening, and how I can
fix it?

Thank you,
Debbie