Rules

Secret ingredients to quality software

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

Last updated by SSW.Rules.SharePointExtractor on 22 Feb 2021 02:03 am (9 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 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 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.

Adam CoganAdam Cogan

We open source. Powered by GitHub