A
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 )
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 )