Syntax Error - Right join

[BigGuy316]

strSQL = "SELECT tblf107grpDept.DeptName AS Org, tblUsers.UserFName + ' ' + tblUsers.UserLName AS Requestor,CONVERT(varchar,
tblF107Log.RecDate) + ' EST' AS [Date Received], tblF107Log.Comments AS Comments, tblF107Log.IDFrm FROM dbo_tblF107grpDept
RIGHT JOIN tblUsers ON tblF107grpDept.IDDept = tblUsers.IDDeptfk RIGHT JOIN tblF107Log ON tblUsers.UserID =
tblF107Log.IDContact WHERE IDFrm = " & request.querystring("IDForm") & ";"
Set rsCoordinationSection = objConn.Execute(strSQL)


This is the error I am getting from the code above:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'tblF107grpDept.IDDept = tblUsers.IDDeptfk RIGHT JOIN tblF107Log ON tblUsers.UserID = tblF107Log.IDContact'.


I am also getting an error stating the convert function is undefined. The SQL was created, I think, for SQL server 2000, but I am trying to get it to work with ACCESS 2003. Any help or suggestions will be greatly appreciated.

Thanks!

[MMcCarthy]

Add in a space before the word CONVERT.

Either remove the dbo_ from the table name dbo_tblF107grpDept or add it in all the time.

Also bracket off the joins as follows:

FROM ((tblF107grpDept RIGHT JOIN tblUsers ON tblF107grpDept.IDDept = tblUsers.IDDeptfk) RIGHT JOIN tblF107Log ON tblUsers.UserID = tblF107Log.IDContact)

strSQL = "SELECT tblf107grpDept.DeptName AS Org, tblUsers.UserFName + ' ' + tblUsers.UserLName AS Requestor,CONVERT(varchar,
tblF107Log.RecDate) + ' EST' AS [Date Received], tblF107Log.Comments AS Comments, tblF107Log.IDFrm FROM dbo_tblF107grpDept
RIGHT JOIN tblUsers ON tblF107grpDept.IDDept = tblUsers.IDDeptfk RIGHT JOIN tblF107Log ON tblUsers.UserID =
tblF107Log.IDContact WHERE IDFrm = " & request.querystring("IDForm") & ";"
Set rsCoordinationSection = objConn.Execute(strSQL)


This is the error I am getting from the code above:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'tblF107grpDept.IDDept = tblUsers.IDDeptfk RIGHT JOIN tblF107Log ON tblUsers.UserID = tblF107Log.IDContact'.


I am also getting an error stating the convert function is undefined. The SQL was created, I think, for SQL server 2000, but I am trying to get it to work with ACCESS 2003. Any help or suggestions will be greatly appreciated.

Thanks!

[BigGuy316]

I tried everything you mentioned, but now I am getting a syntax error message saying error in join operation. Here is the current code:

strSQL = "SELECT tblf107grpDept.DeptName AS Org, tblUsers.UserFName + ' ' + tblUsers.UserLName AS Requestor,

CONVERT(varchar, tblF107Log.RecDate) + ' EST' AS [Date Received], tblF107Log.Comments AS Comments, tblF107Log.IDFrm FROM

((tblF107grpDept Right Join tblUSers on tblF107grpDept.IDDept = tblUsers.IDDeptfk) Right Join tblF107Log on

tblUsers.UserId = tblF107Log.IDContact) WHERE IDFrm = " & request.querystring("IDForm")& ";"

Thanks for the help and suggestion. I still don't know what is wrong.

[MMcCarthy]

FROM ((tblF107grpDept Right Join tblUSers on tblF107grpDept.IDDept = tblUsers.IDDeptfk) Right Join tblF107Log on tblUsers.UserId = tblF107Log.IDContact)

Because you are getting an error on the join expression I would suggest that you do the following:

Check all the table names - if you are using an odbc connection you have to use the table name that is showing in access. However, if you are connecting using ADO straight to the backend or using a SQL pass thru query then you have to use the table name in the SQL backend. Check for dbo. or dbo_ before the table names.

Check all your connection fields - are they of the same type when used in connections. Have you got the field names correct?

Lastly if you are using an odbc connection to link your tables then put the sql statement into a blank query and go to design view. This can sometimes make the problem obvious.

I tried everything you mentioned, but now I am getting a syntax error message saying error in join operation. Here is the current code:

strSQL = "SELECT tblf107grpDept.DeptName AS Org, tblUsers.UserFName + ' ' + tblUsers.UserLName AS Requestor,

CONVERT(varchar, tblF107Log.RecDate) + ' EST' AS [Date Received], tblF107Log.Comments AS Comments, tblF107Log.IDFrm FROM

((tblF107grpDept Right Join tblUSers on tblF107grpDept.IDDept = tblUsers.IDDeptfk) Right Join tblF107Log on

tblUsers.UserId = tblF107Log.IDContact) WHERE IDFrm = " & request.querystring("IDForm")& ";"

Thanks for the help and suggestion. I still don't know what is wrong.