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
- (Expression) Jan =IIf((1 Between DatePart("m",[CTRStartDate]) And DatePart("m",[CTRStopDate])),DSum("CTRRESValue","tblCTRResource","[CTRID]=" & [CTRID])/((DatePart("m",[CTRStopDate])-DatePart("m",[CTRStartDate]))+1))
TYVMIA
Leon