problem with single quote mark

  • Thread starter Thread starter Debbie S.
  • Start date Start date
D

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
 
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
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
 
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
 
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

J_Goddard via AccessMonster.com said:
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
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
Click to expand...
 
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
mscertified said:
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. said:
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
Click to expand...
Click to expand...
 
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 -
Click to expand...
[quoted text clipped - 46 lines]
Click to expand...
 
Try:
= "[TitleOfArticle] = """ & Me![TitleOfArticle] & """"

Debbie S. said:
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
mscertified said:
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. said:
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
Click to expand...
Click to expand...
Click to expand...
 
It works!!! Thank you so much!!!
Debbie
Chris said:
Try:
= "[TitleOfArticle] = """ & Me![TitleOfArticle] & """"

Debbie S. said:
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
mscertified said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top