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?
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 FyeCommented:
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
0

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.
0
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
<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.
0
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
0
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
0
Dale FyeCommented:
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
0
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
0
FordraidersAuthor Commented:
Thanks To All..
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.