By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,710 Members | 1,613 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,710 IT Pros & Developers. It's quick & easy.

Access Query against SQL Server works only without criteria

P: n/a
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?

Jun 23 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
ShastriX (sh******@gmail.com) writes:
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));
~~~~~


Then again, this query does not run on SQL Server. (There is no
date() function in SQL Server and "= False" would yield an error
about missing column.

Presumably some transformation occurs along the way. You could use
the Profiler to see what is actually sent to SQL Server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 23 '06 #2

P: n/a
Hello, ShastriX wrote

I assume that the ForenoonFlag and AfternoonFlag columns have the bit
datatype. I tried to recreate the problem on a smaller table:

CREATE TABLE Firme (
ID_Firma int PRIMARY KEY,
Nume nvarchar(50) NOT NULL UNIQUE,
Furnizor bit NOT NULL DEFAULT (0),
Client bit NOT NULL DEFAULT (0)
-- CHECK (Furnizor<>0 OR Client<>0)
)

I tried running the following Access query:

SELECT dbo_Firme.Nume, [Furnizor] And [Client] AS Expr1
FROM dbo_Firme;

and I saw that Access/Jet will first execute the following SQL query:
SELECT "dbo"."Firme"."ID_Firma" FROM "dbo"."Firme"
and then it will get the values for the Nume, Furnizor and Client
columns, for each of the rows returned, and it will compute Expr1 on
the client side.

When running the following query:

SELECT dbo_Firme.Nume, [Furnizor] And [Client] AS Expr1
FROM dbo_Firme
WHERE ((([Furnizor] And [Client])=0));

then Access/Jet will execute this SQL query:

SELECT "dbo"."Firme"."ID_Firma" FROM "dbo"."Firme" WHERE
((NOT(("Furnizor" = 0 ) ) AND NOT(("Client" = 0 ) ) ) = 0 )

The above query will give the following error: "Line 1: Incorrect
syntax near '='.". The error message is refferring to the last = sign,
because the expression before it is a boolean expression and it is not
possible to compare a boolean expression with an int value (or even a
bit value).

To convince Access to issue a correct SQL query, we can change the
Access query like this:

SELECT dbo_Firme.Nume, [Furnizor] And [Client] AS Expr1
FROM dbo_Firme
WHERE (((dbo_Firme.Furnizor)=0) AND ((dbo_Firme.Client)=0));

In this case, Access will execute the following SQL query:

SELECT "dbo"."Firme"."ID_Firma" FROM "dbo"."Firme" WHERE (("Furnizor" =
0 ) AND ("Client" = 0 ) )

and then it will get all the values for the Nume, Furnizor and Client
columns, to compute Expr1 on the client side.

Razvan

Jun 24 '06 #3

P: n/a
Thanks, Erland, but I had no issue running the query in Access 2003 w/o
criteria.

The hitch came up only when I added the "0" criteria on the SessionOff
computed column.

Regards,
Shastri

Erland Sommarskog wrote:
ShastriX (sh******@gmail.com) writes:

Then again, this query does not run on SQL Server. (There is no
date() function in SQL Server and "= False" would yield an error
about missing column.

Presumably some transformation occurs along the way. You could use
the Profiler to see what is actually sent to SQL Server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se


Jun 26 '06 #4

P: n/a
Thanks a lot for your analysis, Razvan :-) Broke the

SessionOff: ([ForenoonFlag] And [AfternoonFlag]) = 0

into (([ForenoonFlag] = 0) OR ([AfternoonFlag] = 0)). Though this
required repeating all the other criteria for each part of the OR:

((tblWorkDateAttendance.WorkDate)<Date()) AND
((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND
((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND
((tblWorkDate.HolidayFlag)=False)

the query now works like a charm.

Thanks once again,

Shastri

Jun 26 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.