Posted by ShastriX on 06/23/06 12:48

Getting a weird error while trying out a query from Access 2003 on a
SQL Server 2005 table.

Want to compute the amount of leave taken by an emp during the year.
Since an emp might be off for half a day (forenoon or afternoon), have
the following computed field:

SessionOff: ([ForenoonFlag] And [AfternoonFlag])

The query works fine when there's no criterion on SessionOff.

However, when I try to get the records where the SessionOff equals 0, I
get the following error:
~~~~~
ODBC--call failed. [Microsoft][SQL Native Client][SQL server]
Incorrect syntax near the keyword 'NOT'. (#156)
~~~~~

I checked the SQL of the Access query, but there's no NOT anywhere in
it:
~~~~~
SELECT tblWorkDateAttendance.*
FROM tblWorkDate INNER JOIN tblWorkDateAttendance ON
tblWorkDate.WorkDate = tblWorkDateAttendance.WorkDate
WHERE (((([ForenoonFlag] And [AfternoonFlag]))=0) AND
((tblWorkDateAttendance.WorkDate)<Date()) AND
((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND
((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND
((tblWorkDate.HolidayFlag)=False));
~~~~~

What gives?

[Back to original message]