SQL String Or VBA Error (Error Message 3141)

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Could somebody read the function below and tell me if
there is a problem with the SQL syntax or a VBA coding
glitch.
This SQL runs fine when executed via the GUI.
I suspect the root cause is the pesky syntax, I keep
getting (Error Message 3141). Thank you all very much for
your time and effort.The formatting hasn't quite been
duplicated, but I'm sure it clear.


Function BuildTestSQLString2() As Boolean

Dim strSQL As String
Dim qdf As QueryDef
Dim qb As Database

Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qrySike")

strSQL = "SELECT HRBaseTbl.FirstName, HRBaseTbl.Surname,
DepartmentTbl.DeptID, DepartmentTbl.DeptName,
SubDeptTbl.SubDeptName, PositionTbl.PostionName," _
& "FROM PositionTbl RIGHT JOIN (SubDeptTbl RIGHT
JOIN (DepartmentTbl RIGHT JOIN HRBaseTbl ON
DepartmentTbl.DeptID = HRBaseTbl.DeptID) ON
SubDeptTbl.SubDeptID = HRBaseTbl.SubDeptID) ON
PositionTbl.PostionTitleID = HRBaseTbl.PostionID, " _
& "WHERE (((DepartmentTbl.DeptID)=(6))), " _
& "ORDER BY HRBaseTbl.Surname,
DepartmentTbl.DeptName;"


MsgBox "This Is My First SQL String : " & strSQL
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

'MsgBox "This Is My First SQL String : " & strSQL
BuildTestSQLString2 = True
'?BuildTestSQLString
'Hardcoded SQL String
End Function
 
3141=The SELECT statement includes a reserved word or an
argument name that is misspelled or missing, or the
punctuation is incorrect.

I see what appear to be the following:
, FROM
, WHERE
, ORDER

There's three extraneous commas which will trip it right
off.
 
Back
Top