VBA Sql Error

[psycoperl]

HI All, I am getting a VB runtime error when I am running a function (Code Below)
The Error is Runtime Error -2147217900 (800040e14): The SELECT statement includes a reserved word or an argumetn name that is misspelled or missing, or the punctuation is incorrect.

When I run the query in Access's Query Builder, it runs without error.

Can you please provide some guidance.

Thanks

Function CheckIfCurrentATBAppointmentIsPending(StudentID As String, NearestATB As Integer) As Boolean
Dim Flag As Boolean
Dim ATBsql As String
ATBsql = "SELECT MATBS.MaxAppt, ATBS.ATBSession AS SID" & _
         "FROM (SELECT tblATBAppointment.StudentID, Max(tblATBAppointment.AppointmentID) AS MaxAppt " & _
         "      FROM tblATBAppointment " & _
         "      GROUP BY tblATBAppointment.StudentID)  AS MATBS  " & _
         "INNER JOIN tblATBAppointment AS ATBS ON MATBS.MaxAppt = ATBS.AppointmentID " & _
         "WHERE (((MATBS.StudentID)='" & StudentID & "'));"
Flag = False

Dim ATBAppt As ADODB.Recordset
Set ATBAppt = New ADODB.Recordset
ATBAppt.ActiveConnection = CurrentProject.Connection
ATBAppt.CursorType = adOpenDynamic
ATBAppt.LockType = adLockOptimistic
ATBAppt.Open ATBsql

If Not ATBAppt.BOF Then
    ATBAppt.MoveFirst
End If


If ATBAppt.BOF And ATBAppt.EOF Then
'If there are no records, return False to allow scheduling
    Flag = False
ElseIf ATBAppt![SID] < NearestATB Then
'If the returned record is prior to  the date of the nearest ATB, return False to allow scheduling
    Flag = False
Else
    Flag = True
End If

ATBAppt.Close
Set ATBAppt = Nothing

CheckIfCurrentATBAppointmentIsPending = Flag

End Function

 

[Joe4]

For one thing, there is no space between "SID" and "FROM" in youyr first two lines of your SQL construction:

ATBsql = "SELECT MATBS.MaxAppt, ATBS.ATBSession AS SID" & _
"FROM (SELECT tblATBAppointment.StudentID, Max(tblATBAppointment.AppointmentID) AS MaxAppt " & _

In creating SQL code in VBA, I would often have it print out to a Message Box so I can see if I have written it correctly and not made any glaring errrors.

 

[psycoperl]

Thank you that was it!

 

[TerryHogarth21]

BTW, this is really handy if you ever need to copy a SQL Statement from a Access Query to VBA String. http://allenbrowne.com/ser-71.html