Posted by Razvan Socol on 06/24/06 10:22

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

[Back to original message]