By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Migrating Access to SQL "IIF Statement" Issue

P: 53
Hi Guys,

I am a bit of a newbie in these things, and need a little guidance to nudge me in the right direction.

I am having a bit of a problem getting my head around this one, I am attempting to Migrate an MS Access 2003 DB to MS SQL 2005 Express and Access 2003 as the Front End.

I need to Phase out the Value of a Cost Centre around the Months it is valid, ie if the Value is 150,000 and starts in june and finishes in september, it will divide the value by 4 and present the values into Fields named jun through to sept.

[CTRStartDate] and [CTRStopDate] are Date Fields in a table tblCTR.
[CTRRESValue] holds a line value associated with the Record in tblCTR.

I have placed the code into an unbound textbox, but it is very slow, and takes 2 mins to appear on screen with 3,500 Records in tblCTR.

In the Access DB I have a query set up to present this information on screen (see the following code)

Expand|Select|Wrap|Line Numbers
  1.  (Expression) Jan =IIf((1 Between DatePart("m",[CTRStartDate]) And DatePart("m",[CTRStopDate])),DSum("CTRRESValue","tblCTRResource","[CTRID]=" & [CTRID])/((DatePart("m",[CTRStopDate])-DatePart("m",[CTRStartDate]))+1))
Is there some way to do this in SQL?


May 26 '08 #1
Share this Question
Share on Google+
2 Replies

P: 53
I finally got it.

Expand|Select|Wrap|Line Numbers
  1. CASE WHEN 1 BETWEEN datepart(m , tblCTR.CTRStARTDate) AND datepart(m , tblCTR.CTRStopDate) THEN tblCTR.CTRValue / (datepart(m , tblCTR.CTRStopDate) - datepart(m , tblCTR.CTRStartDate) + 1) ELSE NULL END

May 28 '08 #2

Expert Mod 15k+
P: 31,709
Nice one Leon. This is not too easy in T-SQL as you've probably found out by now, so thanks for posting the solution too :)

PS. I'm going to move this across to the SQL Server forum as it's really where it belongs, but to be clear, the OP has already found and posted an answer.
Jun 9 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.