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

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.

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

Fitch: you are a tart!!

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

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.