asked on

tranalate sql server sql to Access sql

I need the following query in Access sql ?

SELECT t_dispensing_metrics_header.AutoID, t_dispensing_metrics_header.Legal_Name,
DateDiff(dd,[Disp MA_End_Date],[Corp_MA_end_Date]) AS Difference,
DateDiff(dd,[Disp MA_End_Date],GETDATE()) as 'Soon',
t_dispensing_metrics_header.[Disp MA_End_Date],
t_dispensing_metrics_header.Corp_MA_End_Date
FROM t_dispensing_metrics_header
where [Corp_MA_end_Date] is not null
and DateDiff(dd,[Disp MA_End_Date],[Corp_MA_end_Date]) > 0
and DateDiff(dd,[Disp MA_End_Date],[Corp_MA_end_Date]) <= 90
and DateDiff(dd,[Disp MA_End_Date],GETDATE()) >-90
order by 4

Thanks
fordraiders

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

<ORDER BY 4>

also work in access and can be translated as

ORDER BY DateDiff(dd,[Disp MA_End_Date],[Corp_MA_end_Date])

which is the 4th column in the query

Dale Fye

<Order By 4>

Rey,

Thanks for that. After 20+ years using Access, I never knew you could do that. Only problem with that is that you never know when someone might change the sequence of the fields in the query.

Nick67

Interesting.
I never knew that was possible.
It, however, does NOT seem to be recommended
http://blog.sqlauthority.com/2010/12/27/sql-server-order-by-columnname-vs-order-by-columnnumber/
http://www.justskins.com/forums/sql-is-it-possible-46695.html
http://www.connectsql.com/2010/12/sql-server-order-by-column-name-or.html

On the other hand, that could be very handy where the order by is a computed column with an alias, as the SQL rarely likes the alias. But there are gotchas, too
http://bradsruminations.blogspot.ca/2010/01/trolls-puzzle-sql-fable.html

Still, good to know

Bitsqueezer

Hi,

you should also be careful in translating "GetDate()" with "Date()". In SQL Server, "GetDate()" is the same as "Now()" in VBA, which means, a date AND time value. That can make a difference in some queries...;-)

Cheers,

Christian

Dale Fye

good catch, Christian! I so rarely use GetDate() in SQL Server that I failed to pickup on that.

Is it just my perception, or have you been conspicuously quiet in EE lately?

Dale

Bitsqueezer

Hi Dale,

yes, you're right, I'm currently really busy because I'm in the hot phase before a release date of my current project at my employer, not really much time for private things...

Cheers,

Christian

Fordraiders

ASKER

Thanks To All..