Syntax error in very long query string...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following sql statement that I want to put into a string sql.

With all the DoubleQuotes, I have tried """ & "h" & """ for instance to put
a "h" into the format statement and somewhere in the sql so the statement is
an absolute monster to follow.

When I dumped the string into a msg box before trying to set the recordset
to the string, it seems to show all the right quotes so I'm not sure where
the error is.


Can someone explain the syntax of how to add quotes into a sqlstring?

Thanks

Here is the Raw Query:
TRANSFORM Count(Hour([tblCallLog]![LogTime])) AS Expr1
SELECT DatePart("h",[LogTime]) AS [Order], Format(([LogTime]),"h am/pm") AS
Hours
FROM tblContactType INNER JOIN (tblBuyers INNER JOIN tblCallLog ON
tblBuyers.BuyerID = tblCallLog.BuyerID) ON tblContactType.ContactTypeID =
tblCallLog.CallType
WHERE (((tblBuyers.ShortName)="Tony") AND ((tblBuyers.BuyerFilter)=True) AND
((tblContactType.BuyerAtDesk)="Yes"))
GROUP BY DatePart("h",[LogTime]), Format(([LogTime]),"h am/pm"),
tblBuyers.BuyerFilter, tblContactType.BuyerAtDesk, tblBuyers.ShortName
ORDER BY DatePart("h",[LogTime]), Format(([LogDate]),"w")-1 & ": " &
Format(([LogDate]),"ddd")
PIVOT Format(([LogDate]),"w")-1 & ": " & Format(([LogDate]),"ddd");

Here is my modified sqlstring:
strQry = "TRANSFORM Count(Hour([tblCallLog]![LogTime])) AS Expr1" _
& "SELECT DatePart(""" & "h" & """,[LogTime]) AS [Order],
Format(([LogTime]),""" & "h am/pm" & """) AS Hours" _
& "FROM tblContactType INNER JOIN (tblBuyers INNER JOIN tblCallLog ON
tblBuyers.BuyerID = tblCallLog.BuyerID) ON tblContactType.ContactTypeID =
tblCallLog.CallType" _
& "WHERE (((tblBuyers.ShortName) = """ & "Tony" & """) And
((tblBuyers.BuyerFilter) = True) And ((tblContactType.BuyerAtDesk) = """ &
"Yes" & """))" _
& "GROUP BY DatePart(""" & "h" & """,[LogTime]), Format(([LogTime]),""" & "h
am/pm" & """), tblBuyers.BuyerFilter, tblContactType.BuyerAtDesk,
tblBuyers.ShortName" _
& "ORDER BY DatePart(""" & "h" & """,[LogTime]), Format(([LogDate]),""" &
"w" & """)& Format(([LogDate]),""" & "ddd" & """)" _
& "PIVOT Format(([LogDate]),""" & "w" & """)-1 & Format(([LogDate]),""" &
"ddd" & """);"
 
I have the following sql statement that I want to put into a string sql.

With all the DoubleQuotes, I have tried """ & "h" & """ for instance to put
a "h" into the format statement and somewhere in the sql so the statement is
an absolute monster to follow.

When I dumped the string into a msg box before trying to set the recordset
to the string, it seems to show all the right quotes so I'm not sure where
the error is.


Can someone explain the syntax of how to add quotes into a sqlstring?
Click to expand...

You can usually use ' as an alternative when you're within a string.
Let's see: ok, one problem is that you're not including blanks in the
string; your AS Expr1 and SELECT will be munged together into
Expr1SELECT, for example. Not sure I'm catching all of them but:

strQry = "TRANSFORM Count(Hour([tblCallLog]![LogTime])) AS Expr1 " _
& "SELECT DatePart(""" & "h" & """,[LogTime]) AS [Order],
Format(([LogTime]),'h am/pm') AS Hours " _
& "FROM tblContactType INNER JOIN (tblBuyers INNER JOIN tblCallLog ON
tblBuyers.BuyerID = tblCallLog.BuyerID) ON
tblContactType.ContactTypeID =
tblCallLog.CallType " _
& "WHERE (((tblBuyers.ShortName) = 'Tony') And
((tblBuyers.BuyerFilter) = True) And ((tblContactType.BuyerAtDesk)
='Yes')) " _
& "GROUP BY DatePart('h',[LogTime]), Format(([LogTime]),'h
am/pm'), tblBuyers.BuyerFilter, tblContactType.BuyerAtDesk,
tblBuyers.ShortName " _
& "ORDER BY DatePart('h',[LogTime]), Format(([LogDate]),'wddd') & _
& "PIVOT Format(([LogDate] - 7),'wddd')

Note that you can combine Format strings in the Format expression, you
don't need to concatenate two Format() functions. Not sure I
understand your PIVOT clause but try tweaking it.

John W. Vinson[MVP]
 
John,
I ended up just saving 2 queries for that one but I was able to apply
something similar in another form by using the following:

Basically just write/save a query with the buyer name being a string that
you won't find anywhere else in the sql..."Tony" in my case, then load the
string in VBA, do a replace on the string with the new filter text, and I
don't have to deal with all the messy quotes and huge sql statement. Works
like a charm... after a few hours of figuring out the syntax of loading a
query in VBA.

Sub Form_Open()
Dim db As Database
Dim qname As String
Dim sqlStr As String
Dim NewBuyerName As String

Set db = CurrentDb
qname = "sqlBuyerWeeklySchedule"
sqlStr = db.QueryDefs(qname).sql
NewBuyerName = [Forms]![frmBuyersInOffice]![lblbuyerclicked].Caption
sqlStr = replace(sqlStr, "Tony", NewBuyerName)
lblTempBuyer.Caption = NewBuyerName
Me.RecordSource = sqlStr
End Sub

John Vinson said:
I have the following sql statement that I want to put into a string sql.

With all the DoubleQuotes, I have tried """ & "h" & """ for instance to put
a "h" into the format statement and somewhere in the sql so the statement is
an absolute monster to follow.

When I dumped the string into a msg box before trying to set the recordset
to the string, it seems to show all the right quotes so I'm not sure where
the error is.


Can someone explain the syntax of how to add quotes into a sqlstring?
Click to expand...

You can usually use ' as an alternative when you're within a string.
Let's see: ok, one problem is that you're not including blanks in the
string; your AS Expr1 and SELECT will be munged together into
Expr1SELECT, for example. Not sure I'm catching all of them but:

strQry = "TRANSFORM Count(Hour([tblCallLog]![LogTime])) AS Expr1 " _
& "SELECT DatePart(""" & "h" & """,[LogTime]) AS [Order],
Format(([LogTime]),'h am/pm') AS Hours " _
& "FROM tblContactType INNER JOIN (tblBuyers INNER JOIN tblCallLog ON
tblBuyers.BuyerID = tblCallLog.BuyerID) ON
tblContactType.ContactTypeID =
tblCallLog.CallType " _
& "WHERE (((tblBuyers.ShortName) = 'Tony') And
((tblBuyers.BuyerFilter) = True) And ((tblContactType.BuyerAtDesk)
='Yes')) " _
& "GROUP BY DatePart('h',[LogTime]), Format(([LogTime]),'h
am/pm'), tblBuyers.BuyerFilter, tblContactType.BuyerAtDesk,
tblBuyers.ShortName " _
& "ORDER BY DatePart('h',[LogTime]), Format(([LogDate]),'wddd') & _
& "PIVOT Format(([LogDate] - 7),'wddd')

Note that you can combine Format strings in the Format expression, you
don't need to concatenate two Format() functions. Not sure I
understand your PIVOT clause but try tweaking it.

John W. Vinson[MVP]
Click to expand...
 
Back
Top