-1

I have a MS Access 2010 query

SELECT
    AirportName,
    EarthDistance(51.1480555555556,  0.190277777777778, Lat, Long, "K") AS Distance
FROM Airports;

it runs correctly, returning all the airports in the table and their distance from London Gatwick.

I can add a where clause such as

WHERE AirportName = "LaGuardia Airport"

and the query runs correctly. However, if I add a where clause such as

WHERE Distance < 5000

I get a dialog box asking me to enter the value of parameter Distance. How can I include this column in a where clause?

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
Steve Waring
  • 2,882
  • 2
  • 32
  • 37
  • 3
    Column aliases cannot be referenced in the WHERE clause. To resolve this, either 1) copy the entire expression in the WHERE clause, or 2) make the original query a nested query in which the column alias can then be referenced. – C Perkins Apr 06 '19 at 20:46
  • @CPerkins Is there any reason why you haven't posted this as an answer? – Zev Spitz Apr 06 '19 at 20:56
  • 2
    Possible duplicate of [Ms-Access: Concatenation, Create Alias, Use Alias](https://stackoverflow.com/questions/4893917/ms-access-concatenation-create-alias-use-alias) – C Perkins Apr 06 '19 at 21:02

1 Answers1

1

Even if you write the SELECT part of a query first, it is executed last. SQL first executes the FROM part, then the WHERE part to filter records, and finally it can SELECT. Therefore the aliases used in the SELECT part are not known to the WHERE part.

You can either repeat the expression in the WHERE part

SELECT
    AirportName,
    EarthDistance(51.1480555555556,  0.190277777777778, Lat, Long, "K") AS Distance
FROM Airports
WHERE EarthDistance(51.1480555555556,  0.190277777777778, Lat, Long, "K") < 5000;

or you can use a subquery as intermediate result

SELECT * FROM
    (SELECT
         AirportName,
         EarthDistance(51.1480555555556,  0.190277777777778, Lat, Long, "K") AS Distance
     FROM Airports) X
WHERE X.Distance < 5000;
Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • It's funny how your brain can work things out overnight. When I awoke I realised I could repeat the expression in the where clause. But I had not thought about using a subquery. If the computation is extensive, the second method might be preferable in this situation. Thank you for eplaining why I could not use the pseudo column name. – Steve Waring Apr 07 '19 at 07:22