CWH Posted February 8, 2002 Share Posted February 8, 2002 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 Link to comment Share on other sites More sharing options...
Fitch Posted February 8, 2002 Share Posted February 8, 2002 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. Link to comment Share on other sites More sharing options...
Vaughan Posted February 8, 2002 Share Posted February 8, 2002 "Hope that helped! If so, Rate Me!" Fitch: you are a tart!! Link to comment Share on other sites More sharing options...
CWH Posted February 9, 2002 Author Share Posted February 9, 2002 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 Link to comment Share on other sites More sharing options...
Fitch Posted February 9, 2002 Share Posted February 9, 2002 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 8103 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now