Data with "Double Quotes"

  • Thread starter Thread starter DP
  • Start date Start date
D

DP

Greetings: In a music application, I have built the
following statement to set the RowSource of cboComposer,
based on the value previously selected in cboTrackTitle:
----------
Dim sSQL As String

sSQL = "SELECT Composer, TrackTitle " & " FROM [qryList:
Composers & Songs] WHERE ([TrackTitle] = """ &
Me.cboTrackTitle & """) ORDER BY [Composer]"

Me.cboComposer.RowSource = sSQL
----------
""" was needed as cboTrackTitle is a text field.

Question: How do you handle double quotes (") in the
cboTrackTitle field? For example a track called - Take
the "A" Train.

Thank you. DP
 
couldn't you write a function like

Function DoubleThisUp(strInput)
DoubleThisUp = Replace(strInput, "/"", "/"/"")
End Function

you'd have to set up a variable to hold your new string first tho ;)

ie

Dim cboTrackTitleNew

DoubleThisUp(Me.cboTrackTitle) = cboTrackTitleNew

I ain't tested this though but it might put you on right lines
 
sorry

Function DoubleThisUp(strInput)
DoubleThisUp = Replace(strInput, "/"", "/"/"")
End Function


Dim cboTrackTitleNew

Me.cboTrackTitle = cboTrackTitleNew

Dim cboTrackTitleNew

DoubleThisUp(cboTrackTitleNew)

is probably more like it

Ric Passey said:
couldn't you write a function like

Function DoubleThisUp(strInput)
DoubleThisUp = Replace(strInput, "/"", "/"/"")
End Function

you'd have to set up a variable to hold your new string first tho ;)

ie

Dim cboTrackTitleNew

DoubleThisUp(Me.cboTrackTitle) = cboTrackTitleNew

I ain't tested this though but it might put you on right lines


DP said:
Greetings: In a music application, I have built the
following statement to set the RowSource of cboComposer,
based on the value previously selected in cboTrackTitle:
----------
Dim sSQL As String

sSQL = "SELECT Composer, TrackTitle " & " FROM [qryList:
Composers & Songs] WHERE ([TrackTitle] = """ &
Me.cboTrackTitle & """) ORDER BY [Composer]"

Me.cboComposer.RowSource = sSQL
----------
""" was needed as cboTrackTitle is a text field.

Question: How do you handle double quotes (") in the
cboTrackTitle field? For example a track called - Take
the "A" Train.

Thank you. DP
Click to expand...
Click to expand...
 
I would have though the triple-double quotes (""") would be breaking it.
Have you tried just escaping the middle one instead of leaving the quotes ?
eg

sSQL = "SELECT Composer, TrackTitle " & " FROM [qryList:
Composers & Songs] WHERE ([TrackTitle] = \" &
Me.cboTrackTitle & \") ORDER BY [Composer]"

This way the quotes should be part of the string, instead of VB interpreting
it as the end of one.
 
""" is appropriate, and \" isn't.

VBA doesn't use \ as an escape character (other than in the Format
function).

DP: If you're only going to have double-quotes in your string, you can
delimit using single-quotes, so that your SQL would look like:

sSQL = "SELECT Composer, TrackTitle " & " FROM [qryList:
Composers & Songs] WHERE ([TrackTitle] = '" &
Me.cboTrackTitle & "') ORDER BY [Composer]"

exagerated for clarity

sSQL = "SELECT Composer, TrackTitle " & " FROM [qryList:
Composers & Songs] WHERE ([TrackTitle] = ' " &
Me.cboTrackTitle & " ' ) ORDER BY [Composer]"

However, I suspect you'll need to be able to handle single quotes as well as
double quotes, so this won't work.

What you need to do is to double the delimiter character in your string. If
you're using " as a delimiter, you'd want "" wherever there's " in your
string. If you're using ', you'd want ''

If you're using Access 2000 or newer, you can use the Replace function to do
this.

sSQL = "SELECT Composer, TrackTitle " & " FROM [qryList:
Composers & Songs] WHERE ([TrackTitle] = """ &
Replace(Me.cboTrackTitle, """", """""") & """) ORDER BY [Composer]"

or

sSQL = "SELECT Composer, TrackTitle " & " FROM [qryList:
Composers & Songs] WHERE ([TrackTitle] = '" &
Replace(Me.cboTrackTitle, "'", "''") & "') ORDER BY [Composer]"

Again exagerating for clarity:

sSQL = "SELECT Composer, TrackTitle " & " FROM [qryList:
Composers & Songs] WHERE ([TrackTitle] = """ &
Replace(Me.cboTrackTitle, " " " ", " " " " " ") & """) ORDER BY [Composer]"

or

sSQL = "SELECT Composer, TrackTitle " & " FROM [qryList:
Composers & Songs] WHERE ([TrackTitle] = ' " &
Replace(Me.cboTrackTitle, " ' ", " ' ' ") & " ' ) ORDER BY [Composer]"

If you're using Access 97 or older, you'll need to write your own equivalent
Replace function.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Nick Howes said:
I would have though the triple-double quotes (""") would be breaking it.
Have you tried just escaping the middle one instead of leaving the quotes ?
eg

sSQL = "SELECT Composer, TrackTitle " & " FROM [qryList:
Composers & Songs] WHERE ([TrackTitle] = \" &
Me.cboTrackTitle & \") ORDER BY [Composer]"

This way the quotes should be part of the string, instead of VB interpreting
it as the end of one.

--
Nick H

DP said:
Greetings: In a music application, I have built the
following statement to set the RowSource of cboComposer,
based on the value previously selected in cboTrackTitle:
----------
Dim sSQL As String

sSQL = "SELECT Composer, TrackTitle " & " FROM [qryList:
Composers & Songs] WHERE ([TrackTitle] = """ &
Me.cboTrackTitle & """) ORDER BY [Composer]"

Me.cboComposer.RowSource = sSQL
----------
""" was needed as cboTrackTitle is a text field.

Question: How do you handle double quotes (") in the
cboTrackTitle field? For example a track called - Take
the "A" Train.

Thank you. DP
Click to expand...
Click to expand...
 
Back
Top