Complex Due Date Calculation

[JoeM]

I have a complex scenario that I am going to do my best to explain. Hopefully it makes sense.
I have a series of 20 "Tests" that our clients plans must go through. Each "Test" has a dynamic "Due Date" associated with it. I am trying to calculate the "Due Date" of each test.

So, I have a table that list the test, and a series of variables that are to be used in the calculation. Some are boolean values, to indicate whether a certain piece of the "Due Date" calculation is to be performed, and then there are integer values (variables) that go along with those calculations. So the structure looks something like this:

strTestName

dteStartDate

blnDateCalc1

intDateCalcVar1

blnDateCalc2

intDateCalcVar2

blnDateCalc3

intDateCalcVar3

…

I realize that this probably doesn't look normalized (and it probably isn't), but I am not sure of how else to do it, because the variables and calculations are not all treated the same (the calculations vary for the the different variables). What I am planning on doing is passing all these variables to a User Defined Function, which calculates the Due Date I need.

Here is where the fun comes in. Different tests will use different "parts" of the calculation (the boolean values determine which ones should be performed). So the UDF is going to perform a series of date calculations. Based on the boolean values, there will be at least one date calculation per record, but could be numerous ones.

So here is my thought. For each "True" boolean value, a calculation will be peformed, and date will be calculated. I was thinking I could then store this calculated date in an array. Then it would go on to the next calculation (still the same record), and keep adding dates to my array. So when it gets through all the intermediate calculations in the UDF, I will be left with an array with at least one date value in it (but could have multiple date values in it).

As the last step, I want to return just the minimum date in my array (that is the value I want the UDF to ultimately return).



Unfortunately, arrays are not my strong suit. So I would need help with is the following:
1. How to set up an array that will have an unknown number of records in it (between 1 and 5 records);
2. How to store values to that array;
3. How to then return the smallest value in the array at the very end.

I am going to stark poking around and see what I can find of arrays, but if someone can point me down the right path, it would be greatly appreciated!

[June7]

You will need to use Redim statement with the array.

Google: Access VBA array

Here is one tutorial http://patorjk.com/programming/tutorials/vbarrays.htm

[pbaldy]

Personal preference, but I rarely use arrays. Wouldn't it be simpler to just compare each calculated value to the previous one, and hang on to the earliest? This type of thing:

ReturnValue = 1st calculation
Variable = 2nd calculation
If Variable < ReturnValue Then ReturnValue = Variable
Variable = 3rd calculation
...

[JoeM]

Personal preference, but I rarely use arrays. Wouldn't it be simpler to just compare each calculated value to the previous one, and hang on to the earliest?

Me too, and I have managed to avoid them thus far!

I thought of that approach too. The only thing that makes it a little tricky is that when the calculations occur. Let's say that there are 7 different calculations. Each starts by looking at the "boolean" value for that calculation, i.e. if it is true, perform the calculation (else move on to the next). So there isn't a set rule/pattern as to where the first calculation will occur (will not be the first If clause if the first boolean value is false).

Just thinking about it has giving me an idea... I could add another variable to my UDF code that checks to see whether any prior calculations have been made, i.e. at the start set the value to False, and set it to True if a calculation is made. That would let me know when I need to do a comparison (and when I don't need to - for the first calculation it comes to). It could work, I just need to add it into each If calculation clause. I was thinking Arrays might be a little more efficient (and it might have a little less redundancy in my code).

I will play around with it tomorrow, starting with the link June provided. If I cannot figure it out, I will probably go the other route.

Thanks to both of you for your suggestions. I will post back and let you know what I did.

[pbaldy]

In that situation, as long as at least one calculation will be made, an option is to preset ReturnValue to a date later than any calculated date would be (like 12/31/2029) Then you simply compare each calculation, and the first one made will update the value.

[JoeM]

June, that link you provided is great! I created a basic example and worked through it to make sure the logic would work, and it appears to.
Here is that basic example, in case it helps others:

Private Sub Command0_Click()
'   see http://patorjk.com/programming/tutorials/vbarrays.htm for tutorial on arrays

    Dim dteMyDate() As Date
    Dim i As Integer
    Dim myMinDate As Date
    
'   Initially dimension the array to hold one value
    ReDim dteMyDate(0) As Date
    
'   Populate first value of array
    dteMyDate(0) = DateValue("12/31/2002")
    
'   If some clause is met where we need to add another value, redim array to allow 2 values and populate
    ReDim Preserve dteMyDate(0 To 1) As Date
    dteMyDate(1) = DateValue("6/29/1971")
    
'   If some clause is met where we need to add another value, redim array to allow 3 values and populate
    ReDim Preserve dteMyDate(0 To 2) As Date
    dteMyDate(2) = DateValue("09/11/1977")
    
'   Initially set min date equal to the first value of the array
    myMinDate = dteMyDate(0)
    
'   Loop through the array, and check dates.  If value is less than min set it to the min
    For i = LBound(dteMyDate) To UBound(dteMyDate)
        If dteMyDate(i) < myMinDate Then myMinDate = dteMyDate(i)
    Next i
        
'   Return min date value
    MsgBox myMinDate
    
End Sub

Now, I am going to go ahead and try to incorporate the same concepts in my complex calculations.

If it all works out, I will mark this as solved. Otherwise, I could be back with follow-up questions.

[JoeM]

I successfully implemented it, and it works!
Thats to all for you help!