want to ask if this code would run?

[markpastoril]

want to ask if this would run in sql server?i've put this code in a query.

so this is the code:

SELECT VehicleMiles.CarNum, VehicleMiles.DorDate, VehicleMiles.SpeedoEnd, (SELECT SpeedoEnd FROM VehicleMiles AS Alias WHERE DorDate = (SELECT Max(DorDate) FROM VehicleMiles AS Alias2 WHERE Alias2.DorDate < VehicleMiles.DorDate AND Alias2.CarNum = VehicleMiles.CarNum) AND Alias.CarNum = VehicleMiles.CarNum) AS PrevEnd, [SpeedoEnd]-[PrevEnd] AS MilesDriven


FROM VehicleMiles
ORDER BY VehicleMiles.CarNum, VehicleMiles.DorDate, VehicleMiles.SpeedoEnd;

[June7]

So what happens? Does it run in Access?

[markpastoril]

yeah, it runs in access, but it won't take effect when I use the sql server database. I wonder how to write it in sql code for me to use it?

[Minty]

What does "won't take effect" mean?

As far as I can tell there is nothing in that SQL statement that SQL Server wouldn't understand.
If you open a new query window in SMSS and run it what error do you get or does it return some results ?

[pbaldy]

I don't see anything that would stop it from running in SQL Server either, but it is unnecessarily complex with the 2 subqueries. Try this:

http://allenbrowne.com/subquery-01.html#AnotherRecord