Problem with Select statement

[irunergoiam]

I'm trying to run the following from an On Click event, but I get a "Compile error: Expected: end of statement" error that highlights the From part of the statement " FROM 1STARTRequestLog ". See code below.

Any ideas what I'm missing?

SQL = "SELECT [1STARTRequestLog].Name AS [START Request For], [ApplicantID] & " - " & [Req] & " - " & [StartSysRequestID] AS [START Request], [1STARTRequestLog].EEID, [1STARTRequestLog].ApplicantID, [1STARTRequestLog].Req, [1STARTRequestLog].[Cost Center], [PL] & " - " & [Dept] AS [PL-Dept], [1STARTRequestLog].Ministry, [1STARTRequestLog].DeptName, [1STARTRequestLog].PositionCode, [1STARTRequestLog].System, [1STARTRequestLog].PermissionLevel, [1STARTRequestLog].DateEmail1, [1STARTRequestLog].TASORepEmail, [1STARTRequestLog].HireType, [1STARTRequestLog].ToSysAdminName, [1STARTRequestLog].ToSysAdminEmail, [1STARTRequestLog].CCSysAdminEmail1, [1STARTRequestLog].CCSysAdminEmail2" _
" FROM 1STARTRequestLog " & _
" WHERE ((([1STARTRequestLog].ApplicantID)=[Forms]![frmNewHireLegalNameVerification]![unbApplicantID]) AND (([1STARTRequestLog].Req)=[Forms]![frmNewHireLegalNameVerification]![unbReq]))" & _
" ORDER BY [1STARTRequestLog].Name; "

 

[HiTechCoach]

looks like tou have an extra " at the end of the previous line that is getting the error

 

[irunergoiam]

This may make more sense (or be even more confusing) Here's the entire code for the event.



Private Sub cmdSendEmail_Click()

Dim rs As DAO.Recordset
Dim sBody As String
Dim sSubject As String
Dim sTo As String
Dim sCC As String
Dim sSQL As String
Me.Refresh
'Set rs = CurrentDb.OpenRecordset("select * from qrySTARTTemplate", dbOpenDynaset, dbSQLPassThrough)
sSQL = "SELECT [1STARTRequestLog].Name AS [START Request For], [ApplicantID] & " - "' & [Req] & '" - " & [StartSysRequestID] AS [START Request], [1STARTRequestLog].EEID, [1STARTRequestLog].ApplicantID, [1STARTRequestLog].Req, [1STARTRequestLog].[Cost Center], [PL] & " - " & [Dept] AS [PL-Dept], [1STARTRequestLog].Ministry, [1STARTRequestLog].DeptName, [1STARTRequestLog].PositionCode, [1STARTRequestLog].System, [1STARTRequestLog].PermissionLevel, [1STARTRequestLog].DateEmail1, [1STARTRequestLog].TASORepEmail, [1STARTRequestLog].HireType, [1STARTRequestLog].ToSysAdminName, [1STARTRequestLog].ToSysAdminEmail, [1STARTRequestLog].CCSysAdminEmail1, [1STARTRequestLog].CCSysAdminEmail2" _
" FROM 1STARTRequestLog " & _
" WHERE ((([1STARTRequestLog].ApplicantID)=[Forms]![frmNewHireLegalNameVerification]![unbApplicantID]) AND (([1STARTRequestLog].Req)=[Forms]![frmNewHireLegalNameVerification]![unbReq]))" & _
" ORDER BY [1STARTRequestLog].Name; "
Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSQLPassThrough)

If rs.RecordCount > 0 Then
Do While Not rs.EOF

sSubject = "START Request " & rs("[START Request]")
sBody = "Dear " & rs("[ToSysAdminName]") & "," & vbNewLine & vbNewLine
sBody = sBody & "Per the START system at Providence Health & Services California, please complete the following system access request: " & vbNewLine & vbNewLine
sBody = sBody & "Employee:" & rs("[START Request for]") & vbNewLine
sBody = sBody & "Employee ID:" & rs("[EEID]") & vbNewLine
sBody = sBody & "Applicant ID:" & rs("[ApplicantID]") & vbNewLine
sBody = sBody & "Req:" & rs("[Req]") & vbNewLine
sBody = sBody & "System:" & rs("[System]") & vbNewLine
sBody = sBody & "Permission:" & rs("[PermissionLevel]") & vbNewLine

sBody = sBody & "Please e-mail confirmation of request completion to START@providence.org" & vbNewLine & vbNewLine
sBody = sBody & "Thank you," & vbNewLine & "START Administrator"
sTo = rs("[ToSysAdminEmail]")
sCC = rs("[CCSysAdminEmail1]")

If sTo <> "" Then
SendEmail sTo, sCC, "", sSubject, sBody
End If
rs.MoveNext
Loop
Else
'msgbox "No records found."
End If '' Recordcount > 0

End Sub

 

[irunergoiam]

Ah I got it! The Select part of the statement I left off the "& _" (the line just before the " From..." part of the statement where the error was occuring.

Cheers...