Calculate Value

[ponderdj]

Hello,

I have a table that includes a baseline Monthly Recurring Charge, and all the months of the fiscal year, starting with october and ending with September. I used a statement to prorate any discontinues by using the "due date" field as a refernce, then zero out all months afterward. It looked like this:

October MRC: IIf(Format([tblISCCSAsbaseline].[due date],"mm")=10,IIf(Format([tblISCCSAsbaseline].[due date],"dd")>=30,Format([tblFinImpact].[Total MRC (Baseline)],"$#,###.00"),Format(((Day([tblISCCSAsbaseline].[due date]))/30)*[tblFinImpact].[Total MRC (Baseline)],"$#,###.00")),IIf(Format([tblISCCSAsbaseline].[due date],"mm")>10 Or Format([tblISCCSAsbaseline].[due date],"mm") Between 1 And 9,Format([tblFinImpact].[Total MRC (Baseline)],"$#,###.00"),0))

November MRC: IIf(Format([tblISCCSAsbaseline].[due date],"mm")=11,IIf(Format([tblISCCSAsbaseline].[due date],"dd")>=30,Format([tblFinImpact].[Total MRC (Baseline)],"$#,###.00"),Format(((Day([tblISCCSAsbaseline].[due date]))/30)*[tblFinImpact].[Total MRC (Baseline)],"$#,###.00")),IIf(Format([tblISCCSAsbaseline].[due date],"mm")>11 Or Format([tblISCCSAsbaseline].[due date],"mm") Between 1 And 9,Format([tblFinImpact].[Total MRC (Baseline)],"$#,###.00"),0))

...

September MRC: IIf(Format([tblISCCSAsbaseline].[due date],"mm")=9,IIf(Format([tblISCCSAsbaseline].[due date],"dd")>=30,Format(0+[NRC],"$#,###.00"),Format((((30-Day([tblISCCSAsbaseline].[due date]))/30)*[tblFinImpact].[total mrc (baseline)])+[NRC],"$#,###.00")),Format([tblFinImpact].[total mrc (baseline)],"$#,###.00"))

These were combined in a large SQL statement.

I then successfully reversed it for use with Start Actions.

Finally, I am now trying to change it to use the "due date" for a change action. As an example I'll use November. This would leave the [Month MRC] alone for the months before the due date (November), prorate the month in which the due date occurs by taking the current MRC (November MRC) times the number of days in the month that will expire before the due date, then take the number of days that will expire after the due date times the new MRC (Which will be in [Total MRC(Baseline)]) and add them together to get that prorated rate for that month, then carry out the baseline MRC for the rest of the fiscal year.

I am struggling with the correct statement. This is what I have so far:

November MRC1: IIf(Format([tblISCCSAsbaseline].[due date],"mm")=10,IIf(Format([tblISCCSAsbaseline].[due date],"dd")>=30,Format([November MRC],"$#,###.00"),Format((30-Day([tblISCCSAsbaseline].[due date])/30)*([tblFinImpact].[total mrc (baseline)])+((Day([tblISCCSAsbaseline].[due date])/30)*([November MRC])),"$#,###.00")),IIf(Format([tblISCCSAsbaseline].[due date],"mm")>10,Format([tblFinImpact].[total mrc (baseline)],"$#,###.00",Format([November MRC]),"$#,###.00")))))

 

[ponderdj]

I'm not sure if it is possible since it is using the field it is meant to update as a part of the calculation.

Any help would be greatly appreciated.

 

[GingerR]

If we had a reward for the most complicated run-on sentence on TT, I'd vote for this one

I'm not an accountant, but my suggestion is to deconstruct your statement: start with the most smallest portion, and see if it works. Then add a bit more, and see if it works. Continue on until you are successful. Frequently when I do this kind of thing, I end up with multiple pieces of calculations filling up lots of columns of a query grid while I try to sort it out.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ponderdj]

Ginger R.

Thanks for the vote. I think that you may be right. This is a case where I'll have to divide the larger problem into subproblems. Thanks for taking the time to read through what I'm trying to accomplish. Now that I'm reading it on Monday morning I have no idea how I expected anyone to understand what I was saying on Friday afternoon. If I have any clear, logical, or comprehensible questions I'll post them.

Thanks for your understanding,
David

 

[ponderdj]

Ok, got one.

I need to replace field one's value with the value of field two in the same table WHERE field 2 IS NOT NULL.

NewTotalMRC1: IIf([tblPracticeMRC].[NewTotalMRC] Is Not Null,[tblPracticeFinImpact].[Total MRC (Baseline)]=[tblPracticeFinImpact].[NewTotalMRC])

This is not working. If I perform it as a select query the value of NewTotalMRC1 is zero.

 

[ponderdj]

Nevermind, figured it out.