By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

SQL Statement - Expected : End of Statement

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
Thanks Don Your suggestion did work for me

Best Regards
Edward

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.