Apostrophes problem!

[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

 

[Jack Leach]

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

 

[PayeDoc]

Jack

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

Thanks again
Les

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

 

[Dale Fye]

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 '.

 

[Jack Leach]

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)

Jack

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

Thanks again
Les

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

 

[Dale Fye]

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

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)

Jack

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

Thanks again
Les

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

 

[Roger Carlson]

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

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)

Jack

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

Thanks again
Les

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

 

[Leslie Isaacs]

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

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

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)

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

 

[Jack Leach]

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)

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

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)

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