473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access Query against SQL Server works only without criteria

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
4 2627
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
5
by: Bernie | last post by:
Greetings, I have 3 servers all running SQL Server 2000 - 8.00.818. Lets call them parent, child1, and child 2. On parent, I create a view called item as follows: CREATE view Item as...
14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
3
by: Peter Phelps | last post by:
My problem is as follows: I need automatically iterate through a single field in a table and use the values in the field to create an in-statement. Currently, the character limitation in the...
0
by: Frederick Noronha \(FN\) | last post by:
---------- Forwarded message ---------- Solutions to Everyday User Interface and Programming Problems O'Reilly Releases "Access Cookbook, Second Edition" Sebastopol, CA--Neither reference book...
10
by: Marizel | last post by:
I'm not sure there's an easy solution to this, but thought I'd ask. I often find myself with a query which I'd like to reuse, but with a different datasource. These datasources generally have...
4
by: BerkshireGuy | last post by:
Our IT department wants to place our Access 2000 tables on an SQL server due to the fact the tables are quite large. With that said, can we still use the Access queries or do we have to do...
6
by: Bob Alston | last post by:
I am looking for Access reporting add-in that would be easy to use by end users. My key focus is on selection criteria. I am very happy with the Access report writer capabilities. As far as...
16
by: JoeW | last post by:
I'm utilizing a database that I created within MS Access within a program I've created in VB.NET. I am using the VB front end to navigate the information, but want to be able to print a report,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.