Apostrophes problem!

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

Hello All

I have a function that returns a string value 'errorfield' (as well as some
other values), which can be over 1000 characters long.
After the function runs, this works fine:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],""" & errorfield & """,""" &
subtype & """)"

but this does not:
MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

The MsgBox action always results in the message:
Was NOT successful, because of " & errorfield & "
- irrespective of the value of 'errorfield.'
In case it made a difference, I have removed any quotes from 'errorfield'
with
errorfield = FindAndReplace([errorfield], """", "")
This succeeds in removing any quotes, but I still get the problem above.

I can't understand why
""" & errorfield & """
is OK in the DoCmd.RunSQL "INSERT statement, but not in the MsgBox action.

Hope someone can help.
Many thanks
Leslie Isaacs
 
When Access see's a quote, it goes into a "quote mode"... now it is inside a
literal string. Once inside that literal string, if it sees a PAIR of
quotes, it adds a single quote to the literal string it's reading, and when
it see's a SINGLE quote, it exits the literal string (I am talking about
actual quotes " rather than apostrophe's ' )


So:

StringVar = "inside quote "" still inside "" still inside "

is equal to

inside quote " still inside " still inside


it gets confusing when you put these all together though. consider...

String = " "" "
String = " " "

The first is a correct statement... a string that equals "
the second is incorrect... access goes into the string, comes back out, goes
into, but there's no coming back out.

MsgBox ("Was NOT successful, because of " & """ & errorfield & """)
Click to expand...

So you last set of quotes needs one more... its entering the quote, reading
a double quote, but not exiting. Also, you have one more contencated quote
than required before the errorfield Try this:

("Was NOT successful, beacuse of """ & errorfield & """")


make any sense? This is always a fun one....

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



PayeDoc said:
Hello All

I have a function that returns a string value 'errorfield' (as well as some
other values), which can be over 1000 characters long.
After the function runs, this works fine:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],""" & errorfield & """,""" &
subtype & """)"

but this does not:
MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

The MsgBox action always results in the message:
Was NOT successful, because of " & errorfield & "
- irrespective of the value of 'errorfield.'
In case it made a difference, I have removed any quotes from 'errorfield'
with
errorfield = FindAndReplace([errorfield], """", "")
This succeeds in removing any quotes, but I still get the problem above.

I can't understand why
""" & errorfield & """
is OK in the DoCmd.RunSQL "INSERT statement, but not in the MsgBox action.

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
 
Jack

Many thanks for your reply ... which I sort of followed ... and which worked
perfectly!!

Thanks again
Les


Jack Leach said:
When Access see's a quote, it goes into a "quote mode"... now it is inside a
literal string. Once inside that literal string, if it sees a PAIR of
quotes, it adds a single quote to the literal string it's reading, and when
it see's a SINGLE quote, it exits the literal string (I am talking about
actual quotes " rather than apostrophe's ' )


So:

StringVar = "inside quote "" still inside "" still inside "

is equal to

inside quote " still inside " still inside


it gets confusing when you put these all together though. consider...

String = " "" "
String = " " "

The first is a correct statement... a string that equals "
the second is incorrect... access goes into the string, comes back out, goes
into, but there's no coming back out.

MsgBox ("Was NOT successful, because of " & """ & errorfield & """)
Click to expand...

So you last set of quotes needs one more... its entering the quote, reading
a double quote, but not exiting. Also, you have one more contencated quote
than required before the errorfield Try this:

("Was NOT successful, beacuse of """ & errorfield & """")


make any sense? This is always a fun one....

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



PayeDoc said:
Hello All

I have a function that returns a string value 'errorfield' (as well as some
other values), which can be over 1000 characters long.
After the function runs, this works fine:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],""" & errorfield & """,""" &
subtype & """)"

but this does not:
MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

The MsgBox action always results in the message:
Was NOT successful, because of " & errorfield & "
- irrespective of the value of 'errorfield.'
In case it made a difference, I have removed any quotes from 'errorfield'
with
errorfield = FindAndReplace([errorfield], """", "")
This succeeds in removing any quotes, but I still get the problem above.

I can't understand why
""" & errorfield & """
is OK in the DoCmd.RunSQL "INSERT statement, but not in the MsgBox action.

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
Leslie,

I have a function I use to wrap things in quotes which makes this process a
lot easier.

Public Function Quotes(TextToQuote As Variant) As String
Dim myText As String

'Handle NULL values
myText = TextToQuote & ""

'Replace " with "" inside the string
myText = Replace(TextToQuote, """", Chr$(34) & Chr$(34))

'Wrap the text in quotes
myText = Chr$(34) & (TextToQuote) & Chr$(34)

Quotes = myText

End Function

I use this function every time I want to wrap a field or a string value in
quotes. Realistically, it is so much easier to read and use than trying to
build SQL strings or other stuff with multiple " or '.
 
Dale's suggestion brings another method I've seen to mind. Also, make it a
point to never use a single quote (') rather than an actual quote (")... it
will fail if you have an apostrophe in a string.

I've seen people do this: In a standard module declare a public constant
for your quote:


Public Const Quo As String = """"

Then, anywhere in your code, use that instead:

MsgBox "This Line and This Var" & Quo & Variable & Quo

makes it much easier...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



PayeDoc said:
Jack

Many thanks for your reply ... which I sort of followed ... and which worked
perfectly!!

Thanks again
Les


Jack Leach said:
When Access see's a quote, it goes into a "quote mode"... now it is inside a
literal string. Once inside that literal string, if it sees a PAIR of
quotes, it adds a single quote to the literal string it's reading, and when
it see's a SINGLE quote, it exits the literal string (I am talking about
actual quotes " rather than apostrophe's ' )


So:

StringVar = "inside quote "" still inside "" still inside "

is equal to

inside quote " still inside " still inside


it gets confusing when you put these all together though. consider...

String = " "" "
String = " " "

The first is a correct statement... a string that equals "
the second is incorrect... access goes into the string, comes back out, goes
into, but there's no coming back out.

MsgBox ("Was NOT successful, because of " & """ & errorfield & """)
Click to expand...

So you last set of quotes needs one more... its entering the quote, reading
a double quote, but not exiting. Also, you have one more contencated quote
than required before the errorfield Try this:

("Was NOT successful, beacuse of """ & errorfield & """")


make any sense? This is always a fun one....

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



PayeDoc said:
Hello All

I have a function that returns a string value 'errorfield' (as well as some
other values), which can be over 1000 characters long.
After the function runs, this works fine:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],""" & errorfield & """,""" &
subtype & """)"

but this does not:
MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

The MsgBox action always results in the message:
Was NOT successful, because of " & errorfield & "
- irrespective of the value of 'errorfield.'
In case it made a difference, I have removed any quotes from 'errorfield'
with
errorfield = FindAndReplace([errorfield], """", "")
This succeeds in removing any quotes, but I still get the problem above.

I can't understand why
""" & errorfield & """
is OK in the DoCmd.RunSQL "INSERT statement, but not in the MsgBox action.

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Jack,

I actually have another version of this function called Wrap, which accepts
an optional character (defaults to ") that you want to use as a wrapper
(could be ", ', or #) and optional parameters for which characters to replace
in the string, and what to replace them with.

I generally use this when I want to wrap date strings in # # or when I want
to write a passthrough query to send to SQL Server (which uses ' instead of
").

----
HTH
Dale



Jack Leach said:
Dale's suggestion brings another method I've seen to mind. Also, make it a
point to never use a single quote (') rather than an actual quote (")... it
will fail if you have an apostrophe in a string.

I've seen people do this: In a standard module declare a public constant
for your quote:


Public Const Quo As String = """"

Then, anywhere in your code, use that instead:

MsgBox "This Line and This Var" & Quo & Variable & Quo

makes it much easier...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



PayeDoc said:
Jack

Many thanks for your reply ... which I sort of followed ... and which worked
perfectly!!

Thanks again
Les


Jack Leach said:
When Access see's a quote, it goes into a "quote mode"... now it is inside a
literal string. Once inside that literal string, if it sees a PAIR of
quotes, it adds a single quote to the literal string it's reading, and when
it see's a SINGLE quote, it exits the literal string (I am talking about
actual quotes " rather than apostrophe's ' )


So:

StringVar = "inside quote "" still inside "" still inside "

is equal to

inside quote " still inside " still inside


it gets confusing when you put these all together though. consider...

String = " "" "
String = " " "

The first is a correct statement... a string that equals "
the second is incorrect... access goes into the string, comes back out, goes
into, but there's no coming back out.


MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

So you last set of quotes needs one more... its entering the quote, reading
a double quote, but not exiting. Also, you have one more contencated quote
than required before the errorfield Try this:

("Was NOT successful, beacuse of """ & errorfield & """")


make any sense? This is always a fun one....

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



:

Hello All

I have a function that returns a string value 'errorfield' (as well as some
other values), which can be over 1000 characters long.
After the function runs, this works fine:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],""" & errorfield & """,""" &
subtype & """)"

but this does not:
MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

The MsgBox action always results in the message:
Was NOT successful, because of " & errorfield & "
- irrespective of the value of 'errorfield.'
In case it made a difference, I have removed any quotes from 'errorfield'
with
errorfield = FindAndReplace([errorfield], """", "")
This succeeds in removing any quotes, but I still get the problem above.

I can't understand why
""" & errorfield & """
is OK in the DoCmd.RunSQL "INSERT statement, but not in the MsgBox action.

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Actually, I ALWAYS use apostrophes -- at first -- and then convert them to
double quotes after. So the first pass of Payedoc's string would be:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],' " & errorfield & " ' , ' " &
subtype & " ' )"

(extra spaces added for clarity only)

Then I convert each apostrophe to TWO quote marks:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],""" & errorfield & """, """ &
subtype & """)"

It helps me to keep it all straight.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Jack Leach said:
Dale's suggestion brings another method I've seen to mind. Also, make it
a
point to never use a single quote (') rather than an actual quote (")...
it
will fail if you have an apostrophe in a string.

I've seen people do this: In a standard module declare a public constant
for your quote:


Public Const Quo As String = """"

Then, anywhere in your code, use that instead:

MsgBox "This Line and This Var" & Quo & Variable & Quo

makes it much easier...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



PayeDoc said:
Jack

Many thanks for your reply ... which I sort of followed ... and which
worked
perfectly!!

Thanks again
Les


Jack Leach said:
When Access see's a quote, it goes into a "quote mode"... now it is
inside a
literal string. Once inside that literal string, if it sees a PAIR of
quotes, it adds a single quote to the literal string it's reading, and when
it see's a SINGLE quote, it exits the literal string (I am talking
about
actual quotes " rather than apostrophe's ' )


So:

StringVar = "inside quote "" still inside "" still inside "

is equal to

inside quote " still inside " still inside


it gets confusing when you put these all together though. consider...

String = " "" "
String = " " "

The first is a correct statement... a string that equals "
the second is incorrect... access goes into the string, comes back out, goes
into, but there's no coming back out.


MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

So you last set of quotes needs one more... its entering the quote, reading
a double quote, but not exiting. Also, you have one more contencated quote
than required before the errorfield Try this:

("Was NOT successful, beacuse of """ & errorfield & """")


make any sense? This is always a fun one....

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



:

Hello All

I have a function that returns a string value 'errorfield' (as well
as some
other values), which can be over 1000 characters long.
After the function runs, this works fine:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],""" & errorfield &
""",""" &
subtype & """)"

but this does not:
MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

The MsgBox action always results in the message:
Was NOT successful, because of " & errorfield & "
- irrespective of the value of 'errorfield.'
In case it made a difference, I have removed any quotes from 'errorfield'
with
errorfield = FindAndReplace([errorfield], """", "")
This succeeds in removing any quotes, but I still get the problem
above.

I can't understand why
""" & errorfield & """
is OK in the DoCmd.RunSQL "INSERT statement, but not in the MsgBox action.

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Dale, Roger and Jack

Many thanks to all of you for these tips - I'll certainly be using them.
I just didn't realise there was so much to it. Nice to have the
workarounds though!

Thanks again
Les

Roger Carlson said:
Actually, I ALWAYS use apostrophes -- at first -- and then convert them to
double quotes after. So the first pass of Payedoc's string would be:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],' " & errorfield & " ' , ' " &
subtype & " ' )"

(extra spaces added for clarity only)

Then I convert each apostrophe to TWO quote marks:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],""" & errorfield & """, """ &
subtype & """)"

It helps me to keep it all straight.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Jack Leach said:
Dale's suggestion brings another method I've seen to mind. Also, make it
a
point to never use a single quote (') rather than an actual quote (")...
it
will fail if you have an apostrophe in a string.

I've seen people do this: In a standard module declare a public constant
for your quote:


Public Const Quo As String = """"

Then, anywhere in your code, use that instead:

MsgBox "This Line and This Var" & Quo & Variable & Quo

makes it much easier...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



PayeDoc said:
Jack

Many thanks for your reply ... which I sort of followed ... and which
worked
perfectly!!

Thanks again
Les


"Jack Leach" <dymondjack at hot mail dot com> wrote in message
When Access see's a quote, it goes into a "quote mode"... now it is
inside
a
literal string. Once inside that literal string, if it sees a PAIR of
quotes, it adds a single quote to the literal string it's reading, and
when
it see's a SINGLE quote, it exits the literal string (I am talking
about
actual quotes " rather than apostrophe's ' )


So:

StringVar = "inside quote "" still inside "" still inside "

is equal to

inside quote " still inside " still inside


it gets confusing when you put these all together though. consider...

String = " "" "
String = " " "

The first is a correct statement... a string that equals "
the second is incorrect... access goes into the string, comes back out,
goes
into, but there's no coming back out.


MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

So you last set of quotes needs one more... its entering the quote,
reading
a double quote, but not exiting. Also, you have one more contencated
quote
than required before the errorfield Try this:

("Was NOT successful, beacuse of """ & errorfield & """")


make any sense? This is always a fun one....

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



:

Hello All

I have a function that returns a string value 'errorfield' (as well
as
some
other values), which can be over 1000 characters long.
After the function runs, this works fine:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],""" & errorfield &
""",""" &
subtype & """)"

but this does not:
MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

The MsgBox action always results in the message:
Was NOT successful, because of " & errorfield & "
- irrespective of the value of 'errorfield.'
In case it made a difference, I have removed any quotes from
'errorfield'
with
errorfield = FindAndReplace([errorfield], """", "")
This succeeds in removing any quotes, but I still get the problem
above.

I can't understand why
""" & errorfield & """
is OK in the DoCmd.RunSQL "INSERT statement, but not in the MsgBox
action.

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
That's a great way to look at it. It may be a little time consuming to do
this manually (I don't see how you could do it in code, without having the
root issue recurr), but that's the most fail-proof method I've seen yet!

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



Roger Carlson said:
Actually, I ALWAYS use apostrophes -- at first -- and then convert them to
double quotes after. So the first pass of Payedoc's string would be:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],' " & errorfield & " ' , ' " &
subtype & " ' )"

(extra spaces added for clarity only)

Then I convert each apostrophe to TWO quote marks:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],""" & errorfield & """, """ &
subtype & """)"

It helps me to keep it all straight.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Jack Leach said:
Dale's suggestion brings another method I've seen to mind. Also, make it
a
point to never use a single quote (') rather than an actual quote (")...
it
will fail if you have an apostrophe in a string.

I've seen people do this: In a standard module declare a public constant
for your quote:


Public Const Quo As String = """"

Then, anywhere in your code, use that instead:

MsgBox "This Line and This Var" & Quo & Variable & Quo

makes it much easier...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



PayeDoc said:
Jack

Many thanks for your reply ... which I sort of followed ... and which
worked
perfectly!!

Thanks again
Les


"Jack Leach" <dymondjack at hot mail dot com> wrote in message
When Access see's a quote, it goes into a "quote mode"... now it is
inside
a
literal string. Once inside that literal string, if it sees a PAIR of
quotes, it adds a single quote to the literal string it's reading, and
when
it see's a SINGLE quote, it exits the literal string (I am talking
about
actual quotes " rather than apostrophe's ' )


So:

StringVar = "inside quote "" still inside "" still inside "

is equal to

inside quote " still inside " still inside


it gets confusing when you put these all together though. consider...

String = " "" "
String = " " "

The first is a correct statement... a string that equals "
the second is incorrect... access goes into the string, comes back out,
goes
into, but there's no coming back out.


MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

So you last set of quotes needs one more... its entering the quote,
reading
a double quote, but not exiting. Also, you have one more contencated
quote
than required before the errorfield Try this:

("Was NOT successful, beacuse of """ & errorfield & """")


make any sense? This is always a fun one....

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



:

Hello All

I have a function that returns a string value 'errorfield' (as well
as
some
other values), which can be over 1000 characters long.
After the function runs, this works fine:

DoCmd.RunSQL "INSERT into employee_submissions
(employee,submission_IRmark,submission_type )
Values([forms]![frm_p45_submission]![namee],""" & errorfield &
""",""" &
subtype & """)"

but this does not:
MsgBox ("Was NOT successful, because of " & """ & errorfield & """)

The MsgBox action always results in the message:
Was NOT successful, because of " & errorfield & "
- irrespective of the value of 'errorfield.'
In case it made a difference, I have removed any quotes from
'errorfield'
with
errorfield = FindAndReplace([errorfield], """", "")
This succeeds in removing any quotes, but I still get the problem
above.

I can't understand why
""" & errorfield & """
is OK in the DoCmd.RunSQL "INSERT statement, but not in the MsgBox
action.

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Back
Top