Syntax Error

[Canadiangal]

Can anyone tell me why I am gettng a syntax error on the following Bolded code:

Private Sub Command18_Click()
Dim rsc As Recordset
Dim ClassNmb As Long


'DoCmd.Beep
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT DISTINCTROW " & _
"DepartmentData.Department, DepartmentData.Title, Employees.LastName, " & _
"Employees.FirstName, Employees.[Employee Number], DepartmentData.[Original Hire Date] " & _
"INTO WrkTable FROM Employees INNER JOIN DeparmentData ON Employees.DepartmentNo = DepartmentData.DepartmentNo"
"ORDER BY DepartmentData.Department, DepartmentData.Title, Employees.LastName, Employees.FirstName;"
DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN Chosen Logical;"
DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN CourseNumber Long;"
DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN Attended Logical;"
DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN DateAttended Date;"
DoCmd.RunSQL "UPDATE WrkTable " & _
"SET WrkTable.CourseNumber = Forms!EdClasses!CourseNumber.Value;"

DoCmd.OpenForm "EdEnroll"
DoCmd.SetWarnings True

End Sub

Thanks in advance for your help.

[pbaldy]

No line continuation characters?

[June7]

If this is an accurate posting of your query, missing a space between DepartmentNo and ORDER BY. When the string compiles the words will run into each other. Also missing line continuation characters.

Why do you need code that creates a new table?

[Canadiangal]

Not sure what No line continuation characters refers to but when I highlight the line of code I do get that message. What is the fix for this?

[pbaldy]

You're missing the

& _

at the end of the previous line.

[Canadiangal]

June, the table is set up to enroll all employees into a class that is being offered. I'm not entirely sure how to add a space. Would this be correct?

"INTO WrkTable FROM Employees INNER JOIN DeparmentData ON Employees.DepartmentNo = DepartmentData.DepartmentNo" +""
"ORDER BY DepartmentData.Department, DepartmentData.Title, Employees.LastName, Employees.FirstName;"
DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN Chosen Logical;"

If so, I am still getting the same syntax error.

[Canadiangal]

Thanks Paul, that did the trick. Any reason why this worked perfectly in Access 95 but not in Access 2007?

[June7]

That was concatenating an empty string.
Just type a space within the quote mark after DepartmentNo., as done on the preceding 3 lines.

Ampersand (&) is preferred operator for concatenation. The + is holdover from ancient Basic.

[pbaldy]

Thanks Paul, that did the trick. Any reason why this worked perfectly in Access 95 but not in Access 2007?

Happy to help. No, and I can't believe that would have worked in previous versions, though I've never used 95.

[June7]

I agree, the sql should have failed in any version.

[Canadiangal]

Still getting an error but not in compiling, just when calling. This is the error: Runtime error 3131 Syntax error in From Clause
This is the code that is highlighted

DoCmd.RunSQL "SELECT DISTINCTROW " & _
"DepartmentData.Department, DepartmentData.Title, Employees.LastName, " & _
"Employees.FirstName, Employees.[Employee Number], DepartmentData.[Original Hire Date] " & _
"INTO WrkTable FROM Employees INNER JOIN DeparmentData ON Employees.EmployeeNumber= DepartmentData.EmployeeNumber, " & _
"ORDER BY DepartmentData.Department, DepartmentData.Title, Employees.LastName, Employees.FirstName;"

[June7]

Remove the comma after EmployeeNumber.

[Canadiangal]

Made that change and now it states that I have a runtime error 3135 Syntax error in Join operation.

DoCmd.RunSQL "SELECT DISTINCTROW " & _
"DepartmentData.Department, DepartmentData.Title, Employees.LastName, " & _
"Employees.FirstName, Employees.[EmployeeNumber], DepartmentData.[Original Hire Date] " & _
"INTO WrkTable FROM Employees INNER JOIN DeparmentData ON Employees.EmployeeNumber = DepartmentData.EmployeeNumber " & _
"ORDER BY DepartmentData.Department, DepartmentData.Title, Employees.LastName, Employees.FirstName;"

[June7]

Why do you even need code to create table?

[Canadiangal]

The table is created on the fly, each time a new class is created an employees get enrolled in the class. Based on this enrollment, reminders are sent, certificates printed out, etc. It may be that an employee would take a class only once for certification, but then they might have to take it yearly.

Thanks
Tina