constructing a string programaticly

[GabberGod]

I need to programaticly construct the string: "'xx1', 'xx2', 'xx3'"

what is the code for putting a ' in a string? and a "?

unfortunately ive never done this and cant find it anywhere in the access help files, but thats not unusual.

 

[Edski]

Dim myString As String, str1 As String
Dim i As Byte

myString = """"    [green]' That's four double quotes
                   ' or myString = Chr(34)[/green]

str1 = "xx"

For i = 1 To 3
  myString = myString & "'" & str1 & i & "', "
Next i

myString = Left(myString, Len(myString) - 2)
myString = myString & Chr(34)

Debug.Print myString

 

[GabberGod]

how does that work? wouldnt access just assume the

myString = """"

is my string = "should be text in here" "should be text in here"

as " is the operator for literal text is it not?

 

[Eupher]

GabberGod,
Do you want the double quotes included in your string? If so, Edski is right - quadrupling the double quotes is one way of indicating a literal double quote character - same as using Chr(34). You're right, of course, the double quote is the delimiter for strings, but there has to be mechanism for the times you want to include a literal double quote within the string.

Access does not interpret a pair of double quotes as "should be text here". "" indicates a null string.

Ken S.

 

[GabberGod]

yes thats what i meant

so if i want after the entire thing is over

myString to read

"'xx1', 'xx2', 'xx3'"

how would i do that

note, its not going to read xx1 in real life that will be a parameter for a query

i.e Select ... where [...] in myString or is null

basicly i have the string, i just cant ge the surrounding punctuation right

 

[crobg]

Edski's code generates:
"'xx1','xx2','xx3'"

If you don't like four double quotes then use the CHR function. CHR(34) returns a quote mark for your script.

 

[GabberGod]

thanks alot guys

i will give edski's code a go.

 

[PHV]

Anyway the SQL syntax for the In operator is:
In ('xx1','xx2','xx3')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GabberGod]

yep you are right

so it ended up looking something like this

Private Sub Combo5_LostFocus()
Dim ctl As Control
Dim varItem As Variant
Dim Str As String

Set ctl = Me.Combo5
Str = """"

For Each varItem In ctl.ItemsSelected
    Str = Str & "'" & ctl.ItemData(varItem) & "', "
Next varItem
Str = Left(Str, Len(Str) - 2)
Str = Str & Chr(34)
Me.Text7 = Str
End Sub