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
LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Very similar syntax between the two except for the DateDiff and GetDate() functions:

SELECT t_dispensing_metrics_header.AutoID
, t_dispensing_metrics_header.Legal_Name
, DateDiff("d",[Disp MA_End_Date],[Corp_MA_end_Date]) AS Difference
, DateDiff("d",[Disp MA_End_Date],DATE()) 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("d",[Disp MA_End_Date],[Corp_MA_end_Date]) >  0
and DateDiff("d",[Disp MA_End_Date],[Corp_MA_end_Date]) <= 90
and DateDiff("d",[Disp MA_End_Date],GETDATE()) >-90

Not sure why you had the ORDER BY 4 line in your TSQL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
Change
GETDATE()) as 'Soon'
to
DATE() as Soon
and change the Datediffs slightly to "d" from dd
and sort out your order by statement, because
order by 4
makes little sense, and you should be good to go, providing you have a table
t_dispensing_metrics_header
in Access, and all the fields are coming from it.
Rey Obrero (Capricorn1)Commented:
<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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions LLCCommented:
<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.
Nick67Commented:
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
BitsqueezerCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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
BitsqueezerCommented:
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
FordraidersAuthor Commented:
Thanks To All..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.