Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
|
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 9th, 2007, 09:58 AM
Registered User
 
Join Date: Mar 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default I am trying to convert a MS Access Query to a SQL?

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!
 
Old March 9th, 2007, 10:21 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
 
Old March 9th, 2007, 10:51 AM
Registered User
 
Join Date: Mar 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old March 9th, 2007, 12:39 PM
Registered User
 
Join Date: Mar 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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,







Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Ms Access report to MS Word doc kite Access 1 October 22nd, 2007 01:45 AM
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 12:33 PM
Convert SQL to MS Access Corey Access 1 February 2nd, 2007 11:46 AM
how to convert ms access db to sql server method SQL Server 2000 1 March 11th, 2005 11:44 PM
MS Access Query using sql/asp redhorse SQL Language 8 March 17th, 2004 05:15 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.