SQL to VBA

[JimLes]

I am getting an error on the AS clause int this SQL when I input it into VGA. Any ideas to fix? Thx!

strSQL = "SELECT qrySeveranceCalculations2.EID," & _
     " tblSeverancedata![First Name] & " " & tblSeverancedata![Last Name] AS [Employee Name]," & _
     " tblSeveranceData.Salary, qrySeveranceCalculations2.[Severance # of Weeks]," & _
     " tblSeveranceData.Division, tblSeveranceData.[Employee's RCN], tblSeveranceData.[Term Date], " & _
     " tblSeveranceData.[Employee Notification Date]" & _
     " FROM qrySeveranceCalculations2 " & _
     " INNER JOIN tblSeveranceData ON qrySeveranceCalculations2.EID = tblSeveranceData.EID" & _
     " WHERE (((tblSeveranceData.[Grade Calc])>23) AND ((tblSeveranceData.[Date Sent ACCT]) Is Null));"

 

[PHV]

strSQL = "SELECT qrySeveranceCalculations2.EID," & _
     " tblSeverancedata![First Name] & [!]' '[/!] & tblSeverancedata![Last Name] AS [Employee Name]," & _
     " tblSeveranceData.Salary, qrySeveranceCalculations2.[Severance # of Weeks]," & _
     " tblSeveranceData.Division, tblSeveranceData.[Employee's RCN], tblSeveranceData.[Term Date], " & _
     " tblSeveranceData.[Employee Notification Date]" & _
     " FROM qrySeveranceCalculations2 " & _
     " INNER JOIN tblSeveranceData ON qrySeveranceCalculations2.EID = tblSeveranceData.EID" & _
     " WHERE (((tblSeveranceData.[Grade Calc])>23) AND ((tblSeveranceData.[Date Sent ACCT]) Is Null));"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Do yourself a favor and try:

strSQL = "SELECT qrySeveranceCalculations2.EID, ...
[blue]
Debug.Print strSQL[/blue]

This way you can SEE your sql in Immediate Window in VBA, and most if the time you can SEE what's going wrong with your statement.

Have fun.

---- Andy

 

[SkipVought]

You might also consider structuring your SQL something like this for clarity...

    Dim sSQL As String
    
    sSQL = "SELECT"
    sSQL = sSQL & "  CAL.EID"
    sSQL = sSQL & ", DTA.[First Name] & ' ' & DTA.[Last Name] AS [Employee Name]"
    sSQL = sSQL & ", DTA.Salary"
    sSQL = sSQL & ", CAL.[Severance # of Weeks]"
    sSQL = sSQL & ", DTA.Division"
    sSQL = sSQL & ", DTA.[Employee's RCN]"
    sSQL = sSQL & ", DTA.[Term Date]"
    sSQL = sSQL & ", DTA.[Employee Notification Date]"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM"
    sSQL = sSQL & "  qrySeveranceCalculations2 CAL INNER JOIN "
    sSQL = sSQL & "  tblSeveranceData          DTA"
    sSQL = sSQL & "    ON CAL.EID = DTA.EID"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE"
    sSQL = sSQL & "      DTA.[Grade Calc]      >23"
    sSQL = sSQL & "  AND DTA.[Date Sent ACCT]  Is Null;"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JimLes]

worked, thanks!!