Hi Edward,
1.) The first thing that I would do in your situation is to add a
"Debug.Print StrSQL" below this code, and then try to run it.
2.) The resulting SQL string will then be available from the Immediate
(Debug) window.
3.) Copy that SQL, then create a new query. Open that query's SQL view and
past in the copied SQL.
4.) Try to switch to datasheet view.
5.) Access will complain, giving you the same "Expected: End of Statement"
error, but this time it will quite likely HIGHLIGHT the offending portion
for you.
6.) My guess is that it will be the ";" at the end of the first line. ";" is
I believe, the signal for the end of the SQL string.
--------------------------------------------------------------------------
All of the above being said... I have one more opinion to express: UGH!
I hate that underscore "line continuation" thing. It really seems to cause
more confusion and problems than it fixes!
Here's what I do instead:
---------------------------
StrSQL = "" 'Clear the string variable
StrSQL = StrSQL & "PARAMETERS [Forms]![FrmAccrualMain]![ProjectGroupID] Long
"
StrSQL = StrSQL & "TRANSFORM Sum(QryBudget.Amount) AS SumOfAmount "
StrSQL = StrSQL & "SELECT QryBudget.[Cost Elements],
QryBudget.SAPAccountName, QryBudget.Order "
StrSQL = StrSQL & "FROM QryBudget WHERE (((QryBudget.ProjectID) = "
StrSQL = StrSQL & [Forms]![FrmAccrualMain]![ProjectGroupID]
StrSQL = StrSQL & ")) "
StrSQL = StrSQL & "GROUP BY QryBudget.[Cost Elements],
QryBudget.SAPAccountName, QryBudget.Order "
StrSQL = StrSQL & "PIVOT Format([Month],"mmm/yy") In (TxtMonths)"
StrSQL = StrSQL & ";"
Debug.Print StrSQL
--
HTH,
Don
=============================
Use
My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop
================================
"Edward S" <so******@qatar.net.qa> wrote in message
news:57*************************@posting.google.co m...
I would appreciate if someone could correct my SQL statement which is
displaying a message Expected : End of Statement this statment is
attached to a button on the form
StrSQL = "PARAMETERS [Forms]![FrmAccrualMain]![ProjectGroupID] Long;"
& _
"TRANSFORM Sum(QryBudget.Amount) AS SumOfAmount" & _
"SELECT QryBudget.[Cost Elements], QryBudget.SAPAccountName,
QryBudget.Order" & _
"FROM QryBudget WHERE (((QryBudget.ProjectID) =
[Forms]![FrmAccrualMain]![ProjectGroupID]))" & _
"GROUP BY QryBudget.[Cost Elements], QryBudget.SAPAccountName,
QryBudget.Order PIVOT Format([Month],"mmm/yy") In (TxtMonths);"
Thanks
Edward
