SQL in code 3

[mayday999]

Hi all,

I have this SQL statement defined in a piece of code, I'm not used to doing this.... : ) i keep getting expected end of statement errors, can anyone tell me what I'm doing wrong?

SQL = "INSERT INTO TblScore ( AssessmentID, ParmsID, SiteID )" &
"SELECT Assessment.AssessmentID, tblAssessmentlink.ParmsID, [Forms]![Form1]![SiteName] AS Expr1" &
"FROM (Assessment INNER JOIN tblAssessmentlink ON Assessment.AssessmentID = tblAssessmentlink.AssessmentID) INNER JOIN tblLParms ON tblAssessmentlink.LParmsID = tblLParms.LParmsID" &
"WHERE (((Assessment.AssessmentID)=[Forms]![Form1]![AssessmentName]));"

Sorry about the formatting, It shows up on three lines in my code and each line ends with " &

TIA

Mayday

 

[PHV]

Lack of space(s):
SQL = "INSERT INTO TblScore ( AssessmentID, ParmsID, SiteID )" &
"[highlight] [/highlight]SELECT Assessment.AssessmentID, tblAssessmentlink.ParmsID, [Forms]![Form1]![SiteName] AS Expr1" &
"[highlight] [/highlight]FROM (Assessment INNER JOIN tblAssessmentlink ON Assessment.AssessmentID = tblAssessmentlink.AssessmentID) INNER JOIN tblLParms ON tblAssessmentlink.LParmsID = tblLParms.LParmsID" &
"[highlight] [/highlight]WHERE (((Assessment.AssessmentID)=[Forms]![Form1]![AssessmentName]));"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mayday999]

Hi PHV

I put the spaces in and I'm getting the same message "expected end of statement for each line. Thanks for the help, is there anything else you can think of?

TIA

Mayday

 

[RickBerem]

Hello,

When you try to make a multi line statement in VBA,
make sure that each line ends with

[Space]_

where [Space] is a real space.

so your code should look like

"INSERT INTO TblScore ( AssessmentID, ParmsID, SiteID )" & _
" SELECT Assessment.AssessmentID...

after each line in the VBA module.

I hope you can understand what I mean and it helps you out.




SG

 

[DJN]

I prefer to make each line seperate with a space at the end. I can see what I am doing then.Just my opinion. It may help.

Dim strSQL As String

strSQL = "INSERT INTO TblScore ( AssessmentID, ParmsID, SiteID ) "
strSQL = strSQL & "SELECT Assessment.AssessmentID AS Expr1, tblAssessmentlink.ParmsID AS Expr2, tblLParms.SiteName "
strSQL = strSQL & "FROM Assessment, tblAssessmentlink, tblLParms;"

DoCmd.RunSQL strSQL

 

[mayday999]

Hi all,

Thanks for all the great suggestions, I must have been missing a space, because I redid the whole thing from scratch and went by all the spacing conventions that were mentioned and it is now working fine.

Thanks again everyone,

mayday

 

[TheAceMan1]

To All . . .
I find it very revealing to delimit lines according to the SQL's [blue]functionality or reserved words![/blue]

[blue] SQL = "INSERT INTO TblScore ( AssessmentID, ParmsID, SiteID )" & _
       "SELECT Assessment.AssessmentID, " & _
              "tblAssessmentlink.ParmsID, " & _
              "[Forms]![Form1]![SiteName] AS Expr1 " & _
       "FROM (Assessment " & _
       "INNER JOIN tblAssessmentlink " & _
       "ON Assessment.AssessmentID = tblAssessmentlink.AssessmentID) " & _
       "INNER JOIN tblLParms " & _
       "ON tblAssessmentlink.LParmsID = tblLParms.LParmsID " & _
       "WHERE (Assessment.AssessmentID=Forms!Form1!AssessmentName);"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .