problem with "

[AxeaXeaxE]

the rowsource of a graphiclooks like this:

SELECT (Format([Schadedatum],"MMM 'YY&quot),Sum([AantalVanSchadenummer]) AS [SomVanAantalVanSchadenummer] FROM [QryOverzichtJaar] GROUP BY (Year([Schadedatum])*12 + Month([Schadedatum])-1),(Format([Schadedatum],"MMM 'YY&quot)

(this rowsource is from the wizard)
I want to use this in VBA, but at the first " VBA expects an instruction-end. How can I avoid this?

 

[AWithers]

try this i.e. replace double quote with single within the string

Dim sql As String

sql = "SELECT (Format([Schadedatum],'MMM' 'YY')),Sum([AantalVanSchadenummer]) AS [SomVanAantalVanSchadenummer] FROM [QryOverzichtJaar] GROUP BY (Year([Schadedatum])*12 + Month([Schadedatum])-1),(Format([Schadedatum],'MMM' 'YY'))"

Andy

 

[AxeaXeaxE]

yeah that was my first idea as well, but that doesn't work...

 

[AxeaXeaxE]

then it says: operator missing

 

[AWithers]

I looked in the help file and think you need to break the string up and use the Chr$(34).

e.g my example
sql = "SELECT (Format([date]," & Chr$(34) & "MMM YY" & Chr$(34) & &quot) from tbldocuments"

Please be aware that as the statement is not an action query you will not be able to run the sql directly using docmd.runsql

Hope this is helpful

Andy

 

[cmmrfrds]

Dim sql as string

sql = "SELECT (Format([Schadedatum],'MMM YY')), "
sql = sql + "Sum ([AantalVanSchadenummer]) AS "
sql = sql + "[SomVanAantalVanSchadenummer] FROM "
sql = sq1 + "[QryOverzichtJaar] GROUP BY "
sql = sql + "(Year([Schadedatum])*12 + "
sql = sql + "Month([Schadedatum])-1),(Format([Schadedatum],"
sql = sql + "'MMM YY'))"

'check out the string if it looks good you should be okay.
Debug.print sql

 

[btaber]

If you want to have the string on multiple lines in VB, add a underscore _ at the end of each line, easier than previous method, both work, but less coding.