Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 converting t-sql queries to MS ACCESS SQL

Author  Topic 

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-20 : 13:22:58
how do i convert this statement to work in MS ACCESS. These are some statements I use in SQL server now they are required for a database in access. They do not work. Anyone knows if it is possible to write them in SQL which would work in access

sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)

sum(case when p.PaymentDate is null and datediff(day, DueDate, getdate()) >30 then 1 else 0 end) [OVER 30]

sum(case when completionDate > Duedate then 1 else 0 end)

case when location = 'NYC' then avg(datediff(day, orderdate, shipdate)) else NULL end [Avg TIME TO SHIP in NYC]


sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-20 : 13:46:05
Why are going backwards?

Can you make Access look like a linked server?

Anyway, You will need to look up Access FUNCTIONS IIF, there is no CASE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-20 : 13:47:32
I know there is no case in access but I tried different statements and its not working
so any help with converting them to access would be appreciated
Go to Top of Page

pootle_flump

1064 Posts

Posted - 2006-09-20 : 16:38:36
Check out Switch or Iif. Also, A SQL for datediff is like:
DateDiff("D", Date1, Date2).

This is regressive though as Brett says. You sure you have to do this?
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-09-20 : 20:05:04
check out the CHOOSE keyword in access. It's somewhat similar to CASE in sql:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctchoose.asp






SqlSpec - a fast, cheap, and comprehensive data dictionary generator for SQL Server 2000 and 2005
Go to Top of Page
   

- Advertisement -