Why has Access altered the code

  • Thread starter Thread starter george
  • Start date Start date
G

george

Hi
Very new at this so here goes.
Read posting from Tom Ellison to Heather re: need to query missing numbers
in list, Just what i needed at the time so used Tom's code.

SELECT AccountNumber + 1 AS BeginRange,
YourTable)
Click to expand...

Typed the code as above with MYTABLE and MYFIELDNAMES but after running the
query the code syntax changed to what you see below.
results are correct and what i expected but why did access change the
code???.
using access 2000 but prog does not seem to like the WHERE NOT EXISTS as
have tried this before with roughly the same results.

SELECT [WelNo]+1 AS BeginRange, (SELECT MIN (WelNo) - 1 FROM NewFormula t1
WHERE t1.WelNo > t.WelNo) AS EndRange
FROM NewFormula AS t
WHERE (((Exists (SELECT * FROM NewFormula t1 WHERE t1.WelNo = t.WelNo +
1))=False) AND ((t.WelNo)<>(SELECT MAX(WelNo)FROM NewFormula)))
ORDER BY [WelNo]+1;


regards
George
 
That's just Access' optimization. The (Exists (SELECT ...)=False) is
Access' way of saying NOT EXISTS (SELECT...).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Hi
Very new at this so here goes.
Read posting from Tom Ellison to Heather re: need to query missing numbers
in list, Just what i needed at the time so used Tom's code.

SELECT AccountNumber + 1 AS BeginRange,
YourTable)
Click to expand...


Typed the code as above with MYTABLE and MYFIELDNAMES but after running the
query the code syntax changed to what you see below.
results are correct and what i expected but why did access change the
code???.
using access 2000 but prog does not seem to like the WHERE NOT EXISTS as
have tried this before with roughly the same results.

SELECT [WelNo]+1 AS BeginRange, (SELECT MIN (WelNo) - 1 FROM NewFormula t1
WHERE t1.WelNo > t.WelNo) AS EndRange
FROM NewFormula AS t
WHERE (((Exists (SELECT * FROM NewFormula t1 WHERE t1.WelNo = t.WelNo +
1))=False) AND ((t.WelNo)<>(SELECT MAX(WelNo)FROM NewFormula)))
ORDER BY [WelNo]+1;
Click to expand...
 
Back
Top