HELP-VBA SQL error! "Expected: Line number or label or statement or end of statement"

[edsac64]

HELP-VBA SQL error! "Expected: Line number or label or statement or end of statement"

Hi, this one has got me going in circles. I created the query in design view and took the SQL code, but I just can't seem to get it cleared up!

mySQL = "INSERT INTO tblUnbilled_BegMonth_ExcludedData ( [Billing Status], LOB, [Area Head Name], [Dept Head Name], [Division Head Name], [Unit Head Name],)" _
"([Manager #], [Manager Name], [Customer Id], [Customer Name], Fund, [CBS Id], [Fund Name], [Fund Type], Cycle, [Billing Frequency], [Billing Month], [Accrual Est], [Last Billed -1], [Last Billed -2], [Last Billed -3], [Date Modified] )" & _
"SELECT tblUnbilled_BegMonth_CleanData.[Billing Status], tblUnbilled_BegMonth_CleanData.LOB, tblUnbilled_BegMonth_CleanData.[Area Head Name], tblUnbilled_BegMonth_CleanData.[Dept Head Name], tblUnbilled_BegMonth_CleanData.[Division Head Name], tblUnbilled_BegMonth_CleanData.[Unit Head Name], tblUnbilled_BegMonth_CleanData.[Manager #], tblUnbilled_BegMonth_CleanData.[Manager Name], tblUnbilled_BegMonth_CleanData.[Customer Id], tblUnbilled_BegMonth_CleanData.[Customer Name], tblUnbilled_BegMonth_CleanData.Fund, tblUnbilled_BegMonth_CleanData.[CBS Id], tblUnbilled_BegMonth_CleanData.[Fund Name], tblUnbilled_BegMonth_CleanData.[Fund Type], tblUnbilled_BegMonth_CleanData.Cycle, tblUnbilled_BegMonth_CleanData.[Billing Frequency], tblUnbilled_BegMonth_CleanData.[Billing Month], tblUnbilled_BegMonth_CleanData.[Accrual Est], tblUnbilled_BegMonth_CleanData.[Last Billed -1], tblUnbilled_BegMonth_CleanData.[Last Billed -2], tblUnbilled_BegMonth_CleanData.[Last Billed -3], tblUnbilled_BegMonth_CleanData.[Date Modified]
"FROM tblUnbilled_BegMonth_CleanData" & _
"WHERE (((tblUnbilled_BegMonth_CleanData.[Billing Month])> '" & crntCBSUnbilledCycle & "'));"

Any help will be sincerely appreciated!
:banghead:

 

[CJ_London]

Re: HELP-VBA SQL error! "Expected: Line number or label or statement or end of statem

I think your problem is in the first line - there is a superflous comma after [Unit Head Name]

 

[edsac64]

Re: HELP-VBA SQL error! "Expected: Line number or label or statement or end of statem

Thanks! That worked for the first line, but now I'm getting the same error for the second line. What are the conventions for using the line separators such as "_" and "& _" ? I'd love to compact the lines so they're easier to read on the code screen. I've been away from this for a long time.

 

[CJ_London]

Re: HELP-VBA SQL error! "Expected: Line number or label or statement or end of statem

Looks like you have brackets where they are not required, actually just realised that line two is a continuation of line 1 in terms of the list of destination fields so in fact, removing the comma was wrong, what should be removed is the ) bracket and on line 2 the ( bracket at the beginning also needs to be removed

[Dept Head Name], [Division Head Name], [Unit Head Name],[COLOR=red])[/COLOR]"[COLOR=red] _
[/COLOR]"[COLOR=red]([/COLOR][Manager #], [Manager Name],

Not sure but also looks like you are missing an ampersand before the _ at the end of line 1 (I prefer to use MySql=MySql & "....)

 

[edsac64]

Re: HELP-VBA SQL error! "Expected: Line number or label or statement or end of statem

Thanks for your help so far. I've run into another wall at this point where I'm getting that nefarious Syntax error (comma) in query expression. In the message box that comes up, it seems to start after the Select statement, but I can't seem to find an absence of a comma. Also, in the Select statement, do I really need to have TableName.[fieldname] for each item? If so, can I create an alias for the table name?

mySQL = "INSERT INTO tblUnbilled_BegMonth_ExcludedData ([Billing Status],[LOB], [Area Head Name], [Dept Head Name],"
mySQL = mySQL & "[Division Head Name], [Unit Head Name], [Manager #], [Manager Name], [Customer Id], [Customer Name],"
mySQL = mySQL & "[Fund], [CBS Id], [Fund Name], [Fund Type], [Cycle], [Billing Frequency], [Billing Month],"
mySQL = mySQL & "[Accrual Est], [Last Billed -1], [Last Billed -2], [Last Billed -3], [Date Modified])" & _
"SELECT (tblUnbilled_BegMonth_CleanData.[Billing Status], tblUnbilled_BegMonth_CleanData.[LOB],"
mySQL = mySQL & "tblUnbilled_BegMonth_CleanData.[Area Head Name], tblUnbilled_BegMonth_CleanData.[Dept Head Name],"
mySQL = mySQL & "tblUnbilled_BegMonth_CleanData.[Division Head Name], tblUnbilled_BegMonth_CleanData.[Unit Head Name],"
mySQL = mySQL & "tblUnbilled_BegMonth_CleanData.[Manager #], tblUnbilled_BegMonth_CleanData.[Manager Name],"
mySQL = mySQL & "tblUnbilled_BegMonth_CleanData.[Customer Id], tblUnbilled_BegMonth_CleanData.[Customer Name],"
mySQL = mySQL & "tblUnbilled_BegMonth_CleanData.[Fund], tblUnbilled_BegMonth_CleanData.[CBS Id],"
mySQL = mySQL & "tblUnbilled_BegMonth_CleanData.[Accrual Est], tblUnbilled_BegMonth_CleanData.[Fund Name],"
mySQL = mySQL & "tblUnbilled_BegMonth_CleanData.[Fund Type], tblUnbilled_BegMonth_CleanData.[Cycle],"
mySQL = mySQL & "tblUnbilled_BegMonth_CleanData.[Billing Frequency], tblUnbilled_BegMonth_CleanData.[Billing Month],"
mySQL = mySQL & "tblUnbilled_BegMonth_CleanData.[Accrual Est], tblUnbilled_BegMonth_CleanData.[Last Billed -1],"
mySQL = mySQL & "tblUnbilled_BegMonth_CleanData.[Last Billed -2], tblUnbilled_BegMonth_CleanData.[Last Billed -3],"
mySQL = mySQL & "tblUnbilled_BegMonth_CleanData.[Date Modified])" & _
" FROM tblUnbilled_BegMonth_CleanData" & _
" WHERE (((tblUnbilled_BegMonth_CleanData.[Billing Month])> '" & crntCBSUnbilledCycle & "'));"

 

[boblarson]

Re: HELP-VBA SQL error! "Expected: Line number or label or statement or end of statem

You need a space before the word SELECT. As it stands right now you have a run on sentence of

[Date Modified])SELECT

And Yes, you can create a table alias.