"sql " in vb- how to represent these "

[capdownlondon]

im trying to paste some sql behind a combobox in vb, i wont explain it all. but basically, i know i have to have the sql in " SQL " but what do i do if the sql contains "" within itself, then it is causing problems. is there either something i can put infront of the " like \" for example so that this works?
someone please help.

thanks

adam

 

[missinglinq]

You really need to post your SQL code in order for anyone to help you.

 

[capdownlondon]

ok well, the code is:

Private Sub Combo5_AfterUpdate()
   
Me.OLEUnbound0.RowSource = TRANSFORM Sum(redscount2.[CountOfCard Code]) AS [SumOfCountOfCard Code]
SELECT (Format([fldDate],"dd mm"" '""yy")) AS Expr1
FROM redscount2
WHERE (((redscount2.[Childs Name]) = "frankie graham"))
GROUP BY redscount2.[Childs Name], (Year([fldDate])*12+Month([fldDate])-1), (Format([fldDate],"dd mm"" '""yy"))
PIVOT redscount2.[Card Code];

   
End Sub

but i think if i'm right it needs to have " " and the & _ (i've seen this in other code doig similar stuff) round it looking more like this:

Private Sub Combo5_AfterUpdate()
   
Me.OLEUnbound0.RowSource = "TRANSFORM Sum(redscount2.[CountOfCard Code]) AS [SumOfCountOfCard Code]" & _
"SELECT (Format([fldDate],"[COLOR="Red"]dd[/COLOR] mm"" '""yy")) AS Expr1" & _
"FROM redscount2 " & _
"WHERE (((redscount2.[Childs Name]) = [COLOR="Green"]"frankie graham"[/COLOR]))" & _
"GROUP BY redscount2.[Childs Name], (Year([fldDate])*12+Month([fldDate])-1), (Format([fldDate],"dd mm"" '""yy"))" & _
"PIVOT redscount2.[Card Code];"



   
End Sub

but then it gets hung up (as highlighted in red), and i'm assuming this is due to the "" within the sql code.
but also where (highighted green) i want to get this value from the combo, where i believe

"WHERE (((redscount2.[Childs Name]) = " & Me.Combo5.Value

should do this.

 

[capdownlondon]

managed to work it out.

Private Sub Combo5_AfterUpdate()
Dim sManagerSources As String
Dim strMyDate As String

strMyString = "dd mm yy"

sManagerSources = "TRANSFORM Sum(redscount2.[CountOfCard Code]) AS [SumOfCountOfCard Code] SELECT (Format([fldDate],'dd mm yy')) AS Expr1 FROM redscount2 WHERE (((redscount2.[Childs Name]) = " & Chr$(34) & Me.Combo5.Value & Chr$(34) & _
")) GROUP BY redscount2.[Childs Name], (Year([fldDate])*12+Month([fldDate])-1), (Format([fldDate],'dd mm yy')) PIVOT redscount2.[Card Code];"

Debug.Print sManagerSources


Me!OLEUnbound0.RowSource = sManagerSources
Me!OLEUnbound0.Requery


End Sub