Dealing with quoted strings

  • Thread starter Thread starter Bill Stanton
  • Start date Start date
B

Bill Stanton

In the following segment of code:

NewWorksSQL = "INSERT INTO [Works](Title,compid,orchid,condid,genreid"
NewWorksSQL = NewWorksSQL & ",soloid,volid,PlayingTime,LastPlayed)"
NewWorksSQL = NewWorksSQL & " VALUES(""" & WorksTitle & """, " & CompID
& ", " & OrchID & ", "
NewWorksSQL = NewWorksSQL & CondID & ", " & GenreID & ", " & SoloID & ",
"
NewWorksSQL = NewWorksSQL & VolID & ", """ & TTime & """, " & LastPlayed
& ")"
CurrentDb.Execute NewWorksSQL, dbFailOnError

the Execute fails if the user enters a quoted string
amidst the textbox "WorksTitle". I understand why
it fails, but I don't know how to fix it.

Do I have to parse the string entered by the user
and make substitutions using the Chr function?
(Got to be a better way than that... I hope)

Thanks,
Bill
 
Bill,

Try it like this...

...NewWorksSQL & " VALUES(" & """" & WorksTitle & """" & ", " & CompID..
 
Hi Bill/Steve:

Steve- I think that his problem is that folks can enter a name, s.a. BIRD
(without quotes), or "BIRD" (with quotes), and he needs to be able to handle
both situations.

First of all, a possible error-
If TTime is a time variable, it should go like:

FROM-
NewWorksSQL = NewWorksSQL & VolID & ", """ & TTime & """, " & LastPlayed

TO-
NewWorksSQL = NewWorksSQL & VolID & ", # " & TTime & " # ," & LastPlayed

Now for your problem, Bill-
"WorksTitle". I understand why it fails, but I don't know how to fix it.

I guess you want to simply get rid of the quotes, if present. First of all,
if you search the MS Access online help, you'll find the listed "Character
Set(0-127)", and for the quote character, the number is 34. This is
important to know, as you can now search each character in the phrase
entered by the enduser with a "For... Next" process, as such:

dim ii as string, iii as integer, xx as string, yy as string, zz as string

zz = ""

ii = [WorksTitle]
For iii = 0 to Len(ii)-1
xx= mid(ii,iii,1)
yy= Asc(xx)

'If yy=34 then the value is a quote, so let's get rid of it
' rebuilding the number with ONLY alphanumeric digits:
If yy = 34 then
zz = "" ' if a quote is found, then don't add anything to the zz rebuilt
phrase
else
zz = zz & xx ' if not a quote, then use the letter/numeric to rebuild the
entry phrase
endif
Next

Now that you've sterilized the [WorksTitle] entry from any quotes, you won't
get any more errors.

Regards,
Al

___________________________
Al Borges, MD
Arlington Medical Group, PC
Arlington, Virginia

Steve Schapel said:
Bill,

Try it like this...

..NewWorksSQL & " VALUES(" & """" & WorksTitle & """" & ", " & CompID..

--
Steve Schapel, Microsoft Access MVP


Bill said:
In the following segment of code:

NewWorksSQL = "INSERT INTO [Works](Title,compid,orchid,condid,genreid"
NewWorksSQL = NewWorksSQL & ",soloid,volid,PlayingTime,LastPlayed)"
NewWorksSQL = NewWorksSQL & " VALUES(""" & WorksTitle & """, " & CompID
& ", " & OrchID & ", "
NewWorksSQL = NewWorksSQL & CondID & ", " & GenreID & ", " & SoloID & ",
"
NewWorksSQL = NewWorksSQL & VolID & ", """ & TTime & """, " & LastPlayed
& ")"
CurrentDb.Execute NewWorksSQL, dbFailOnError

the Execute fails if the user enters a quoted string
amidst the textbox "WorksTitle". I understand why
it fails, but I don't know how to fix it.

Do I have to parse the string entered by the user
and make substitutions using the Chr function?
(Got to be a better way than that... I hope)

Thanks,
Bill
Click to expand...
Click to expand...
 
Hi Bill/Steve:

Steve- I think that his problem is that folks can enter a name, s.a. BIRD
(without quotes), or "BIRD" (with quotes), and he needs to be able to handle
both situations.

First of all, a possible error-
If TTime is a time variable, it should go like:

FROM-
NewWorksSQL = NewWorksSQL & VolID & ", """ & TTime & """, " & LastPlayed

TO-
NewWorksSQL = NewWorksSQL & VolID & ", # " & TTime & " # ," & LastPlayed

Now for your problem, Bill-
"WorksTitle". I understand why it fails, but I don't know how to fix it.

I guess you want to simply get rid of the quotes, if present. First of all,
if you search the MS Access online help, you'll find the listed "Character
Set(0-127)", and for the quote character, the number is 34. This is
important to know, as you can now search each character in the phrase
entered by the enduser with a "For... Next" process, as such:

dim ii as string, iii as integer, xx as string, yy as string, zz as string

zz = ""

ii = [WorksTitle]
For iii = 0 to Len(ii)-1
xx= mid(ii,iii,1)
yy= Asc(xx)

'If yy=34 then the value is a quote, so let's get rid of it
' rebuilding the number with ONLY alphanumeric digits:
If yy = 34 then
zz = "" ' if a quote is found, then don't add anything to the zz rebuilt
phrase
else
zz = zz & xx ' if not a quote, then use the letter/numeric to rebuild the
entry phrase
endif
Next

Now that you've sterilized the [WorksTitle] entry from any quotes, you won't
get any more errors.

Regards,
Al

___________________________
Al Borges, MD
Arlington Medical Group, PC
Arlington, Virginia

Steve Schapel said:
Bill,

Try it like this...

..NewWorksSQL & " VALUES(" & """" & WorksTitle & """" & ", " & CompID..

--
Steve Schapel, Microsoft Access MVP


Bill said:
In the following segment of code:

NewWorksSQL = "INSERT INTO [Works](Title,compid,orchid,condid,genreid"
NewWorksSQL = NewWorksSQL & ",soloid,volid,PlayingTime,LastPlayed)"
NewWorksSQL = NewWorksSQL & " VALUES(""" & WorksTitle & """, " & CompID
& ", " & OrchID & ", "
NewWorksSQL = NewWorksSQL & CondID & ", " & GenreID & ", " & SoloID & ",
"
NewWorksSQL = NewWorksSQL & VolID & ", """ & TTime & """, " & LastPlayed
& ")"
CurrentDb.Execute NewWorksSQL, dbFailOnError

the Execute fails if the user enters a quoted string
amidst the textbox "WorksTitle". I understand why
it fails, but I don't know how to fix it.

Do I have to parse the string entered by the user
and make substitutions using the Chr function?
(Got to be a better way than that... I hope)

Thanks,
Bill
Click to expand...
Click to expand...
 
What I need is to be able to retain whatever the
end-user enters, including a quoted word or phrase
within the textbox. Example:

Symphony No. 2 In G Minor "Winter Dreams", Op.13

(That's Tchaikovsky for you classical buffs.)

So, do I have to parse WorksTitle such that I end up with:

str1 = "Symphony No. 2 In G Minor"
str2 = " Winter Dreams"
str3 = ", Op.13"

and modify the SQL insertion to read:

"'"" & str1 & Chr(34) & str2 & Chr(34) & str3 & """, "

Thanks,
Bill
 
Bill,

Did you try my earlier suggestion? I'd be very surprised if it doesn't
do what you want.
 
Steve,
The resulting expression is identical to what I already
had. I put a msgbox after the assignment statement to
see the exact expression being passed to the SQL
Execute. Both yours and mine read:

Value("My string with a "quoted word" display this way",.......

The offending statement generates a 3075 runtime error:
Missing operator in Query expression
'"My string with a "quoted word" display this way"'

Bill
 
Bill said:
In the following segment of code:

NewWorksSQL = "INSERT INTO [Works](Title,compid,orchid,condid,genreid"
NewWorksSQL = NewWorksSQL & ",soloid,volid,PlayingTime,LastPlayed)"
NewWorksSQL = NewWorksSQL & " VALUES(""" & WorksTitle & """, " & CompID
& ", " & OrchID & ", "
NewWorksSQL = NewWorksSQL & CondID & ", " & GenreID & ", " & SoloID & ",
"
NewWorksSQL = NewWorksSQL & VolID & ", """ & TTime & """, " & LastPlayed
& ")"
CurrentDb.Execute NewWorksSQL, dbFailOnError

the Execute fails if the user enters a quoted string
amidst the textbox "WorksTitle". I understand why
it fails, but I don't know how to fix it.
Click to expand...


When you don't know if the string might contain either
single and/or double quotes, then you ***must*** double up
whichever quote you use outside the string:

NewWorksSQL = NewWorksSQL & " VALUES(""" & _
Replace(WorksTitle, """", """""") & """, " & CompID
 
BINGO!!!!!!!!!!!!!!!!

That did the trick Marsh.

Thanks to Al, March & Steve. It has been
a thorn in my side for awhile now as to how
to get the string "presentation" to SQL done
correctly.

Thanks again,
Bill



Marshall Barton said:
Bill said:
In the following segment of code:

NewWorksSQL = "INSERT INTO [Works](Title,compid,orchid,condid,genreid"
NewWorksSQL = NewWorksSQL & ",soloid,volid,PlayingTime,LastPlayed)"
NewWorksSQL = NewWorksSQL & " VALUES(""" & WorksTitle & """, " & CompID
& ", " & OrchID & ", "
NewWorksSQL = NewWorksSQL & CondID & ", " & GenreID & ", " & SoloID & ",
"
NewWorksSQL = NewWorksSQL & VolID & ", """ & TTime & """, " & LastPlayed
& ")"
CurrentDb.Execute NewWorksSQL, dbFailOnError

the Execute fails if the user enters a quoted string
amidst the textbox "WorksTitle". I understand why
it fails, but I don't know how to fix it.
Click to expand...


When you don't know if the string might contain either
single and/or double quotes, then you ***must*** double up
whichever quote you use outside the string:

NewWorksSQL = NewWorksSQL & " VALUES(""" & _
Replace(WorksTitle, """", """""") & """, " & CompID
Click to expand...
 
well, i don't think its as bad as that :>

functions have already been written to do this kind of thing.

ie. from mvps.org/access

(call it like
txtbox=FindAndReplace(txtbox, """", "'")
and if txtbox was = "WorksTitle" (including quotes)
it would now be = 'WorksTitle'
which should suit your sql fine


Code
-------------------

'This code was originally written by Alden Streeter.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Alden Streeter
'
Function FindAndReplace(ByVal strInString As String, _
strFindString As String, _
strReplaceString As String) As String
Dim intPtr As Integer
If Len(strFindString) > 0 Then 'catch if try to find empty string
Do
intPtr = InStr(strInString, strFindString)
If intPtr > 0 Then
FindAndReplace = FindAndReplace & left(strInString, intPtr - 1) & _
strReplaceString
strInString = Mid(strInString, intPtr + Len(strFindString))
End If
Loop While intPtr > 0
End If
FindAndReplace = FindAndReplace & strInString
End Function
'************ Code End **********
 
How does your suggestion differ from what
Marshall offered? Does FindAndReplace
pre-date the availability of Replace as a
primitive in VBA 2000?

Bill
 
Back
Top