Converting T-SQL -> Access give Syntax error (missing operator) inquery expression

[Andrew John]

Dear bilingual people,

I'm trying to use Access 2000 as a front end to SQL2000,
using ODBC linked tables.

I have written a query that works fine directly in SQL2000
( Query Analyser ), but I cannot seem to get it into a format
that Access will digest. The error is :
Syntax error (missing operator) in query expression ''.

Although that last could be ". rather than ''. It's hard to tell in
the message box that pops up.

I've tried changing single quotes to double, removing aliases,
aggregate and order clauses to no avail - the error just won't go away.

Anyone know what Access is choking over ?

The query is:
SELECT
ts.FiscalMonth,
tsl.Notes as TimeSheetNote,
sum(tsl.HoursNorm) as SumNormalHours
FROM tblTimeSheetLines AS tsl
inner join tblTimesheets as ts
on ts.TimesheetNo = tsl.TimesheetNo
and ts.AdjustmentNo = tsl.AdjustmentNo
and ts.Instance = tsl.Instance
inner join tblJobs as j
on j.Instance = tsl.Instance
and j.JobNo = tsl.JobNo
WHERE left(j.LOB, 1) in ( '4', '7' ) -- HPS Group
and left(tsl.Notes, 3) in ('IRW', 'ERW' )
group by tsl.Notes, ts.FiscalMonth
order by ts.FiscalMonth, tsl.Notes

I've narrowed it down to something in the join.

SELECT
tsl.Notes as TimeSheetNote,
ts.EmpNo
FROM tblTimeSheetLines AS tsl
inner join tblTimesheets as ts
on ts.TimesheetNo = tsl.TimesheetNo
and ts.AdjustmentNo = tsl.AdjustmentNo
and ts.Instance = tsl.Instance
inner join tblJobs as j
on j.Instance = tsl.Instance
and j.JobNo = tsl.JobNo
WHERE left(tsl.Notes, 3) in ('IRW', 'ERW' )

does not work, yet

SELECT
tsl.Notes as TimeSheetNote,
ts.EmpNo
FROM tblTimeSheetLines AS tsl
inner join tblTimesheets as ts
on ts.TimesheetNo = tsl.TimesheetNo
and ts.AdjustmentNo = tsl.AdjustmentNo
and ts.Instance = tsl.Instance
WHERE left(tsl.Notes, 3) in ('IRW', 'ERW' )

does.

regards
AJ

p.s. ( No politically I cannot put the code in
a stored procedure and just call it from Access )

 

[Van T. Dinh]

T-SQL and Access / JET SQL syntaxes are different.

In JET SQL, you need parentheses for each JOIN except the outermost JOIN.

OTOH, if you already have an SQL String that works in SQL Server, it is much
easier to use the same SQL String in a Pass-Through Query. It will also be
more efficient as the processing is done by the SQL Server in the back-end
and only the selected rows are returned to the front-end.

Check Access Help on Pass-Through Query.

 

[Albert D. Kallal]

Why not just paste the sql that works into a view. So, just create a view on
the server side..and then LINK to that table on the ms-access side.

Since you are using joins between tables, then you are MUCH better off to
have the join occur server side..and NOT on the ms-access side. So, for
reasons of performance (and, in your case sql syntax), just put the sql
server side...and link to that view. (so, I don't see any need here for a
stored procedure on the server side anyway. (and, you mention you can't use
stored procedures). However, I see no need to use a stored procedure
here....but you certainly can and should use a view. Using a view will give
you MUCH better performance, and your query can be put on the server side
with the old syntax that worked. (so, we kill two birds with one stone!).

Perhaps even simpler then creating a view on the server side, and linking to
it...you will find it somewhat easier to just paste that query from the
server (that works) into the ms-access query builder. However, just make the
query pass through. This again also will mean that ms-access is complete
ignored, and the whole query is sent to the serer un-touched. (so, if for
some strange reasons you can't create view either..then just make your query
pass through).

Hence, use a view..and link to that view on the ms-access side. Or, just use
a pass through query.

 

[Andrew John]

Pass through query - thanks guys I'll give that a try

( No I can't put ANYTHING server side )

Thanks again
AJ