Do you avoid parameter queries with EXISTS keyword and comparison operators (<> or =)(Upsizing Problem)?

Last updated by Brook Jeynes [SSW] 7 months ago.See history

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 - 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 - Access query with EXISTS keyword and without comparison operator

In 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.

We open source. Powered by GitHub