Usage of ' or " in SQL String 1

[chrisjones99]

Hello,

I'm trying to get a SQL string working in a file but get very confused with the usage of ' or ".
Can you help ? Thanks a lot in advance.
I always get error in that SQL string.
I used one of the FAQ in order to build up the query but still got some problems : Here it is



Sub PLbycountry()

Dim strSQL As String

Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef
'set variable values
Set dbs = CurrentDb
strQueryName = "BobQuery"
'Delete old query first - we want fresh data!
dbs.QueryDefs.Delete strQueryName
'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us.
strSQL = "TRANSFORM Sum([REVENUE]*-1) AS Expr2" & _
"SELECT [Mapping P&L table].[SortingP&L], [Mapping P&L table].Grouping, [Mapping P&L table].[PL group]" & _
"FROM [Mapping Countries Pivot<> ISC] INNER JOIN ([Mapping P&L table] INNER JOIN (SUMMNAME INNER JOIN (REGIONS INNER JOIN ([PRODUCT/SERVICE] INNER JOIN (ITMPRO INNER JOIN REVENUE2 ON ITMPRO.CODE = REVENUE2.CODE) ON [PRODUCT/SERVICE].[G/L ACCT NO] = REVENUE2.[G/L ACCT NO]) ON REGIONS.ORG = REVENUE2.ORG) ON SUMMNAME.[CUSTOMER NO] = REVENUE2.[CUSTOMER NO]) ON [Mapping P&L table].[Prod and Services] = [PRODUCT/SERVICE].[PRODUCT/SERVICE]) ON [Mapping Countries Pivot<> ISC].ORG1 = REGIONS.ORG" & _
"WHERE (((REGIONS.REGION) Not Like '" & "UBN" & "') AND ((REGIONS.[MANAGEMENT ROLLUP])='" & "emea" & "') AND ((REVENUE2.[G/L ACCT NO]) Not Like '" & "44*" & "' And (REVENUE2.[G/L ACCT NO]) Not Like '" & "4???81" & "' And (REVENUE2.[G/L ACCT NO]) Not Like '" & "48*" & "') AND ((REVENUE2.ORG) Not Like '" & "356862" & "') AND ((REGIONS.SUBSIDIARY)='" & "NO&""') AND (([Mapping Countries Pivot<> ISC].[Countries as Pivot])='" & "Infonet Germany" & "'))" & _
"GROUP BY [Mapping P&L table].[SortingP&L], [Mapping P&L table].Grouping, [Mapping P&L table].[PL group]" & _
"ORDER BY [Mapping P&L table].[SortingP&L], [Mapping P&L table].Grouping, [Mapping P&L table].[PL group]" & _
"PIVOT Format([DATE]," & "mmm" & ") In ('" & "APR" & "," & "MAY" & "," & "JUN" & "," & "JUL" & "," & "AUG" & "," & "SEP" & "," & "OCT" & "," & "NOV" & "," & "DEC" & "," & "JAN" & "," & "FEB" & "," & "MAR" & ");"

'Create query definition
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
'Open report for viewing
DoCmd.OpenReport "BobReport", acViewPreview
' *** End of Code ***
Beep
MsgBox "query finished", vbOKOnly, ""

End Sub


Any help would be greatly appreciated.

Thanks!

Chrisjones99

 

[dhookom]

First of all, I wouldn't delete and then recreate the query. All you need to do is change the SQL property of the existing query.

You are using "Like" in your where but haven't provided any wild cards. You might as well use "=".

Your code doesn't add spaces (at least 6 instances) like here:

[Mapping P&L table].[PL group]" & _
"FROM

Your where and pivot clause might be better expressed:

"WHERE (((REGIONS.REGION) <> 'UBN') AND ((REGIONS.[MANAGEMENT ROLLUP])='emea') AND ((REVENUE2.[G/L ACCT NO]) Not Like '44*' And (REVENUE2.[G/L ACCT NO]) Not Like '4???81' And (REVENUE2.[G/L ACCT NO]) Not Like '48*') AND ((REVENUE2.ORG) <> '356862') AND ((REGIONS.SUBSIDIARY)='NO&') AND (([Mapping Countries Pivot<> ISC].[Countries as Pivot])='Infonet Germany')) " & _
"GROUP BY [Mapping P&L table].[SortingP&L], [Mapping P&L table].Grouping, [Mapping P&L table].[PL group] " & _
"ORDER BY [Mapping P&L table].[SortingP&L], [Mapping P&L table].Grouping, [Mapping P&L table].[PL group] " & _
"PIVOT Format([DATE],'mmm') In ('APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC','JAN','FEB','MAR');"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chrisjones99]

Thanks a lot for your explanation, I will try it right now