Having Trouble Sorting...

[vennardk]

Working with Access 97 (willing to try your ideas even if your not sure! )

I have a query that calculates the distance between a Zip code entered and a list of cities. What I need is to sort by closest cities to the Zip.

Here is my code. I'll explain what I've tried below it.

SELECT CommunityClasses.Community, (69.1*(CommunityClasses.Latitude-Zip.Lat)) AS X, ((69.1*(CommunityClasses.Longitude-Zip.Lng))*Zip.keviekev) AS Y, Sqr((X^2)+(Y^2)) AS Distance
FROM CommunityClasses, Zip
WHERE (((Zip.[Zip Code])=[Please enter a ZIP Code]));

When I try to sort, it asks for a value for X and then again for Y. If I don't ask to sort, it calculates the values as it should do.

If I ask it to ORDER BY Sqr((X^2)+(Y^2)) it again asks for values of X and Y.

What do I need to do to sort the Distance Field in Asending order.

Thanks for any input! Let me know if I did not give enough details.

 

[RoyVidar]

I think the ORDER BY clause is evaluated prior to the select, so it doesn't know about those aliases. You'll need something along the lines of

...
ORDER BY Sqr(((69.1*(CommunityClasses.Latitude-Zip.Lat))^2)+(((69.1*(CommunityClasses.Longitude-Zip.Lng))*Zip.keviekev)^2))

Edit: but you could probably also just sort by ordinal position

ORDER BY 3

 

[vennardk]

Worked like a charm! Perfect! Thank you very much!