|
Subject:
|
I am trying to convert a MS Access Query to a SQL?
|
|
Posted By:
|
WebLadyBug
|
Post Date:
|
3/9/2007 8:58:31 AM
|
I am trying to convert a MS Access Query to a SQL query and I need help with some IIF Statements?
2 in particular that I've encountered problems with are:
Count(IIf([elphoursflag]=0 Or [elphoursflag]=2,IIf([envelopes].isitecode=[logsheets].isitecode,IIf([recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])),[zip]))))'AS wk2count,
AND
Format(Avg(IIf([elphoursflag]=0 Or [elphoursflag]=2,IIf([envelopes].isitecode=[logsheets].isitecode,IIf([recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])),[elphours])))),[0.0])AS wk2avg,
Any help that someone could offer would be greatly appreciated!
Thanks!
|
|
Reply By:
|
dparsons
|
Reply Date:
|
3/9/2007 9:21:14 AM
|
Are you upsizing the Database through Enterpirse Manager or doing it manually?
===========================================================
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
===========================================================
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470131470.html
===========================================================
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
===========================================================
|
|
Reply By:
|
WebLadyBug
|
Reply Date:
|
3/9/2007 9:51:38 AM
|
I used the Upsizing wizard to do most of the conversion, but the majority of the access queries are having to be transferred over manually.
|
|
Reply By:
|
WebLadyBug
|
Reply Date:
|
3/9/2007 11:39:01 AM
|
I was able to get it working with some more effort. I figured I would post my results.
Count(case
when [elphoursflag]=0 Or [elphoursflag]=2 then
case
when [envelopes].isitecode=[logsheets].isitecode then
case
when [recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])) then
[zip]
end
end
end) AS wk2count,
Round(Avg(case
when [elphoursflag]=0 Or [elphoursflag]=2 then
case
when [envelopes].isitecode=[logsheets].isitecode then
case
when [recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd(day,13,[dFirstPSYDate])) then
elphours
end
end
end), 0.0) AS wk2avg,
|
