Home
Do you avoid parameter queries with EXISTS keyword and comparison operators (<> or =)(Upsizing Problem)?
  v2.0 Posted at 20/10/2010 7:56 PM by System Account

The MS Upsizing Wizard cannot upsize Microsoft Access queries containing

  • EXISTS <> FALSE/TRUE or
  • EXISTS = FALSE/TRUE

For example, the following query will not be upsized:

PARAMETERS [@Employee Last Name] Text ( 20 );    
SELECT Orders.OrderID
, Orders.CustomerID
, Orders.EmployeeID
FROM Orders
WHERE EXISTS (SELECT EmployeeID
 FROM Employees 
 WHERE LastName= [@Employee Last Name] 
 AND Employees.EmployeeID=Orders.EmployeeID) <> FALSE
Figure: Bad example of Access query with EXISTS keyword and comparison operator
PARAMETERS [@Employee Last Name] Text ( 20 ); 
SELECT Orders.OrderID
, Orders.CustomerID
, Orders.EmployeeID

FROM Orders
WHERE EXISTS (SELECT EmployeeID FROM Employees
WHERE LastName= [@Employee Last Name] AND Employees.EmployeeID=Orders.EmployeeID)
Figure: Good example of Access query with EXISTS keyword and without comparison operatorIn order to get the good example syntax you must switch from Design View window to SQL View in query designer window and save query definition.

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: