Migrating Access to SQL "IIF Statement" Issue

[Echidna]

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)

 (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?

TYVMIA



Leon

[Echidna]

I finally got it.

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

Cheers

Leon

[NeoPa]

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.