error with query after change from access 2007 to 2010

Question

I have made a MS Access db app and It works correct.

When I want change computer and version of my access from 2007 to 2010. When i want see result of my query it shows error. May i change my query or it is some other mistake?

My query:

SELECT Kalendar.id_kalendar, Os_udaje.Meno, Os_udaje.Priezvisko, Os_udaje.id_os_udaje
FROM Os_udaje, Kalendar
WHERE ((((Kalendar.id_kalendar) Between [Pociatocný dátum] And [Koncový dátum]))) 
AND ((Kalendar.volno)=No) 
AND ((Kalendar.vikend)=No) 
AND (((Os_udaje.Nastupil)< Kalendar.id_kalendar ) AND ((Os_udaje.Odisiel)>Kalendar.id_kalendar)) 
AND ((NOT Exists (SELECT *
              FROM Pracoval
              WHERE Os_udaje.id_os_udaje = Pracoval.id_os_udaje
              AND Kalendar.id_kalendar = Pracoval.id_kalendar))) 
AND ((NOT Exists (SELECT *
              FROM REZERVACIA 
              WHERE Kalendar.id_kalendar BETWEEN REZERVACIA.platnost_od AND REZERVACIA.platnost_do 
              AND Os_udaje.id_os_udaje = REZERVACIA.id_os_udaje))) 
AND ((NOT Exists (SELECT *
              FROM DOVOLENKA 
              WHERE Kalendar.id_kalendar BETWEEN DOVOLENKA.od AND DOVOLENKA.do 
              AND Os_udaje.id_os_udaje = DOVOLENKA.id_os_udaje)));

Error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

Answer 1

Problem was in stettings of my operating system. I have formating of data set to "1. 1. 2012" with space and i am using in access format without space "1.1.2012". When i chane date format in windows all starts work correct.

Answer 2

Regarding the fact that this query executes in Access 2007 but not so in Access 2010: it is hard to say why this may be the case, since most of the published differences between the two deal with specific data types and not of specifications of allowable SQL syntax.

As other comments have suggested, I would guess the culprit lies in the AND NOT EXISTS (SELECT * FROM ...) conditionals.

That being said, I will propose an equivalent query (in theory), and tips on boosting its performance.

Simplified Example

First, let's tackle what this query is trying to accomplish using words. You are seeking a cross join (cartesian product) of tables Os_udaje and Kalendar, which have certain related fields and removing rows which have related records meeting two conditions in three different tables. The latter requirement is accomplished by the NOT EXISTS clauses, and this is what we want to re-write.

Take for example:

SELECT TableA.Field1, TableB.Field2
FROM TableA, TableB WHERE
NOT EXISTS (SELECT *
    FROM TableC
    WHERE TableA.Field1=TableC.Field1
    AND TableB.Field2=TableC.Field2);

Without going into the details of why, we can re-write this query as a three table cross join with a different set of WHERE conditionals:

SELECT TableA.Field1, TableB.Field2
FROM TableA, TableB, TableC WHERE
    (TableA.Field1=TableC.Field1)
    AND
    (TableB.Field2<>TableC.Field2);

Full Equivalent Query

Applying this relationship to the original query, we have:

SELECT Kalendar.id_kalendar, Os_udaje.Meno, Os_udaje.Priezvisko, Os_udaje.id_os_udaje
FROM Os_udaje, Kalendar, Pravocal, REZERVACIA, DOVOLENKA
WHERE ((((Kalendar.id_kalendar) Between [Pociatocný dátum] And [Koncový dátum]))) 
AND ((Kalendar.volno)=No) 
AND ((Kalendar.vikend)=No) 
AND (((Os_udaje.Nastupil)< Kalendar.id_kalendar ) AND ((Os_udaje.Odisiel)>Kalendar.id_kalendar)) 
AND (
    Os_udaje.id_os_udaje = Pracoval.id_os_udaje
    AND Kalendar.id_kalendar <> Pracoval.id_kalendar) 
AND (
    Kalendar.id_kalendar BETWEEN REZERVACIA.platnost_od AND REZERVACIA.platnost_do 
    AND Os_udaje.id_os_udaje <> REZERVACIA.id_os_udaje) 
AND (
    Kalendar.id_kalendar BETWEEN DOVOLENKA.od AND DOVOLENKA.do 
    AND Os_udaje.id_os_udaje <> DOVOLENKA.id_os_udaje);

Tips on Boosting Performance

Since this query is doing a 5-table cross join, it could be very inefficient (take the product of the number of rows in each). Two techniques to boost the performance are to:

Use INNER JOIN statements instead of full Cartesian joins:

SELECT * FROM
(SELECT  * FROM Table1,Table2) As SubQry 
INNER JOIN
Table3
ON (SubQry.Field2=Table3.Field2 AND SubQry.Field1<>Table3.Field1);

Perform sub-query conditionals first to reduce the number of rows:

SELECT Kalendar2.id_kalendar, Os_udaje.Meno, Os_udaje.Priezvisko, Os_udaje.id_os_udaje
FROM Os_udaje,
(SELECT * FROM Kalendar
WHERE ((((Kalendar.id_kalendar) Between [Pociatocný dátum] And [Koncový dátum]))) 
AND ((Kalendar.volno)=No) 
AND ((Kalendar.vikend)=No)) AS Kalendar2,
Pravocal,
...

Possible Full Answer

I cannot test this query, and do not know if BETWEEN statements work as JOIN conditionals, but here is the answer using joins and nested subqueries:

SELECT Kalendar.id_kalendar, Os_udaje.Meno, Os_udaje.Priezvisko, Os_udaje.id_os_udaje
FROM
(((
    SELECT Kalendar.id_kalendar, Os_udaje.Meno, Os_udaje.Priezvisko, Os_udaje.id_os_udaje
    FROM Os_udaje, Kalendar
    WHERE ((((Kalendar.id_kalendar) Between [Pociatocný dátum] And [Koncový dátum]))) 
    AND ((Kalendar.volno)=No) 
    AND ((Kalendar.vikend)=No) 
    AND (((Os_udaje.Nastupil)< Kalendar.id_kalendar ) AND ((Os_udaje.Odisiel)>Kalendar.id_kalendar))
) As SubQry
INNER JOIN
Pravocal
ON
    (SubQry.id_os_udaje = Pracoval.id_os_udaje
    AND SubQry.id_kalendar <> Pracoval.id_kalendar))
INNER JOIN
REZERVACIA
ON
    (SubQry.id_kalendar BETWEEN REZERVACIA.platnost_od AND REZERVACIA.platnost_do 
    AND SubQry.id_os_udaje <> REZERVACIA.id_os_udaje))
INNER JOIN
DOVOLENKA
ON
    (SubQry.id_kalendar BETWEEN DOVOLENKA.od AND DOVOLENKA.do 
    AND SubQry.id_os_udaje <> DOVOLENKA.id_os_udaje);