easy answer?

[CWH]

I'm trying to devise a script/calculation that will look at 8 date fields (DO1, DO2 etc) and if the value is 1/1/02...12/31/02, a value in a corresponding number field (AO1, AO2 etc) will be added to a total figure. In other words, there are 8 quarterly payments scheduled over 24 months, but I want to get a total of just the payments that will fall in a certain date range. It seems as if it should be simple, but the only thought I have requires a script with 8 different subscripts.

Suggestions appreciated.

Chuck

[Fitch]

Easy answer: calculated fields... wrong forum, oh well, here goes:

Make a calculated number that =

((Year(D01) = 2002) * A01) +

((Year(D02) = 2002) * A02) +

((Year(D03) = 2002) * A03) +

etc.

In other words, the first part of the expression yields a 1 (true) or 0 (false), then multiply by the field and add it up!

If you want to make it more flexible, replace "2002" with a number field, e.g. MyYear, or an expression that evaluates start and end dates. Think about it and if you still need help let me know.

[Vaughan]

"Hope that helped! If so, Rate Me!"

Fitch: you are a tart!!

[CWH]

Hey, that's great! Thank you. It works like a charm. I did run in to trouble when I tried setting up a more flexible field (YearToUse). The year has to be entered on every record I want to sum, which I can do quickly using the Replace function. It should probably be a Global Field. I haven't worked with Global Fields, but I guess now's a good time to start.

Thanks again,

Chuck

[Fitch]

You're right, I should have specified global fields, that's definitely the way to go.

Like this:

((D01 globalStartDate and D01 globalEndDate) * A01) +

((D02 globalStartDate and D02 globalEndDate) * A02) +

((D03 globalStartDate and D03 globalEndDate) * A03) +

etc.