Max of Multiple columns, with constraints

[copleyr]

Hello all,

I am trying to put together a function in a query that will identify and return the correct date in a record. For example, in the below chart:

For my function to work, I need it to return the next upcoming "Scheduled" date, without skipping to another level if there is a blank "Actual" date not listed in the next level. If a "Scheduled" and "Actual" date is filled for level 1, then the function can choose the next "Scheduled" date for the 2nd level. As in, if there is already an actual date in levels 1 and 2, it would choose the next "Scheduled" date in level 3, if there is one. If there is not a "Scheduled" date in level 3, then it can return "Not Required".

In short:

· If actual date is null and schedule date isnull then “Not Required”. Otherwise, Min of the "Scheduled" dates where "Actual" date isnull


Is there a way I can do this in a module with VBA? Thank you in advance for your help!!

 

[pbaldy]

Re: Max of Multiple columns, with contstraints

This should give you a start:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

Pass it all the values, and have it stop testing if it hits a Null. If the first value is Null return Not Required.

 

[copleyr]

Re: Max of Multiple columns, with contstraints

This should give you a start:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

Pass it all the values, and have it stop testing if it hits a Null. If the first value is Null return Not Required.

Thank you sir. How would I change the code up to be able to do that? My apologies as I'm not familiar with this type of logic.

 

[pbaldy]

Re: Max of Multiple columns, with contstraints

You can use IsNull() to test for Null, Exit Function to break out of the loop. Dive in and see how it goes.

 

[copleyr]

Re: Max of Multiple columns, with contstraints

You can use IsNull() to test for Null, Exit Function to break out of the loop. Dive in and see how it goes.

I was unsure how to apply VBA, so I tried this approach:

Scheduled Return: IIf((IsNull([kdrivecrm701]![Scheduled RDG Date]) And IsNull([kdrivecrm701]![Approved RDG Date]) And IsNull([kdrivecrm701]![Pursuit Decision Scheduled]) And IsNull([kdrivecrm701]![Bid Decision Scheduled]) And IsNull([kdrivecrm701]![Validate Bid Scheduled]) And IsNull([kdrivecrm701]![Submit Proposal Scheduled] And IsNull([kdrivecrm701]![LevelIIScheduledDate])),"Not Required",IIf(IsNull([kdrivecrm701]![Presented RDG Date]),[kdrivecrm701]![Scheduled RDG Date],IIf(IsNull([kdrivecrm701]![Actual L0 Submit Date]),[kdrivecrm701]![Approved RDG Date],IIf(IsNull([kdrivecrm701]![PursuitDecision-1A]),[kdrivecrm701]![Pursuit Decision Scheduled], IIf(IsNull([kdrivecrm701]![BidDecision-1B]), [kdrivecrm701]![Bid Decision Scheduled], IIf(IsNull( [kdrivecrm701]![ValidateBidDecision-2]), [kdrivecrm701]![Validate Bid Scheduled], IIf(IsNull([kdrivecrm701]![SubmitProposalActual]), [kdrivecrm701]![Submit Proposal Scheduled], [kdrivecrm701]![LevelIIScheduledDate]))))))))

I had many more levels to do but I wanted to convey the logic behind what I was trying to accomplish. When I entered all the 7 levels, I came back with an error that "The expression you entered has a function containing too many arguments". I'm unsure what happened here. I also ran out of argument space, as I had two more remaining arguments to make at the end..

Stuck now at this moment

 

[pbaldy]

Re: Max of Multiple columns, with contstraints

Can you post a database with sample data?

 

[copleyr]

Re: Max of Multiple columns, with contstraints

Here you go. I tried to make it as simple as possible. The last column in the query is what I am trying to return.

 

[pbaldy]

Re: Max of Multiple columns, with contstraints

I made the assumption that levels would be filled out in order, in other words level 1, then 2, etc. Based on the sample data, that appears to have been a false assumption. What gets returned if the level 1 fields are empty but others have values?

 

[copleyr]

Re: Max of Multiple columns, with contstraints

This means that there is in error in the data and it needs to be fixed by one of our analysts. In this case, can it return "check data"?

 

[pbaldy]

Re: Max of Multiple columns, with contstraints

I don't want to overly complicate the function, and that can be found with a query. See how this works. Note that the function assumes the values will be entered in a specific order, that is scheduled then actual.

 

[copleyr]

Re: Max of Multiple columns, with contstraints

I have checked it and so far it looks EXCELLENT. Our analysts have alot of cleanup to do. Many thanks for your time. You are my savior!

 

[pbaldy]

Re: Max of Multiple columns, with contstraints

Happy to help. Like I said, it would be a fairly simple query to find records where the level 1 fields were Null and any others weren't.

 

[copleyr]

Re: Max of Multiple columns, with contstraints

Thanks again for your help!

This code worked out perfectly for what I was trying to accomplish at the time:

Option Compare Database
Option Explicit

Function Maximum1(ParamArray FieldArray() As Variant)
  ' Declare the two local variables.
  Dim I                       As Integer
  Dim currentVal              As Variant

  ' Set the variable currentVal equal to the array of values.
  currentVal = FieldArray(0)

  If IsNull(FieldArray(0)) Then    'the first value is null, no need to continue
    Maximum1 = "Not required"
    Exit Function
  End If

  ' Cycle through each value from the row to find the largest.

  For I = 0 To UBound(FieldArray)
    If I Mod 2 = 0 Then    'it's even, a scheduled value
      If FieldArray(I) > currentVal Then
        currentVal = FieldArray(I)
      End If
    Else    'it's odd, an actual value
      If IsNull(FieldArray(I)) Then    'actual is null, use last scheduled max
        Maximum1 = currentVal
        Exit Function
      End If
    End If
  Next I

  ' Return the maximum value found.
  Maximum1 = currentVal

End Function

Is there a way I can have it ignore blanks? For example:

For Record 1, can I still return a 10/8/12, without it returning "Not Required"?

Thank you in advance!

 

[copleyr]

Re: Max of Multiple columns, with contstraints

bumping because im desperate to make this work

 

[pbaldy]

Re: Max of Multiple columns, with contstraints

In my view it does work, within the constraints mentioned (you said that condition was an error that needed to be fixed). I'd use an error checking query to locate the records that needed attention.

If you want the function to handle it, you'd have to have some variables to test whether the first field was Null and then another wasn't, and handle the different situations accordingly. It would add to the complexity, which is why I suggested using a query.

 

[copleyr]

Re: Max of Multiple columns, with contstraints

In my view it does work, within the constraints mentioned (you said that condition was an error that needed to be fixed). I'd use an error checking query to locate the records that needed attention.

If you want the function to handle it, you'd have to have some variables to test whether the first field was Null and then another wasn't, and handle the different situations accordingly. It would add to the complexity, which is why I suggested using a query.

That was my mistake. If one level is blank, then it doesn't necessarily mean it's an error; it just means that there was not a scheduled or actual date for that level.

Therefore the logic still applies:

If array of actual dates isnull and schedule dates isnull then “Not Required”. Otherwise, Min of the "Scheduled" dates where "Actual" date isnull

I just need the code to ignore blank entries and continue on. Is that possible or would it be too complex?

 

[pbaldy]

Re: Max of Multiple columns, with contstraints

I'm not sure we have a clear set of rules, but you can try taking out the test for Null before the loop out, and adding a test for currentVal still being Null at the end.

 

[copleyr]

Re: Max of Multiple columns, with contstraints

I'm not sure we have a clear set of rules, but you can try taking out the test for Null before the loop out, and adding a test for currentVal still being Null at the end.

I'm not sure if I want to take this out:

Else    'it's odd, an actual value
      If IsNull(FieldArray(I)) Then    'actual is null, use last scheduled max
        Maximum1 = currentVal
        Exit Function
      End If

because I still want it to see if the actual is null, then to use the last scheduled max. I think that is what we need.

I do think we need to take this out:

  If IsNull(FieldArray(0)) Then    'the first value is null, no need to continue
    Maximum1 = "Not required"
    Exit Function
  End If

If we used this logic (especially for Record 1), I think we would be golden:

Please let me know if it's too complex, I can hope to try something else. I basically just need it to ignore blank levels.

 

[pbaldy]

Re: Max of Multiple columns, with contstraints

I said the test "before the loop", so the latter.

 

[copleyr]

Re: Max of Multiple columns, with contstraints

I said the test "before the loop", so the latter.

So something like this? :

Option Compare Database
Option Explicit

Function Maximum1(ParamArray FieldArray() As Variant)
  ' Declare the two local variables.
  Dim I                       As Integer
  Dim currentVal              As Variant

  ' Set the variable currentVal equal to the array of values.
  currentVal = FieldArray(0)


  ' Cycle through each value from the row to find the largest.

  For I = 0 To UBound(FieldArray)
    If I Mod 2 = 0 Then    'it's even, a scheduled value
      If FieldArray(I) > currentVal Then
        currentVal = FieldArray(I)
      End If
    Else    'it's odd, an actual value
      If IsNull(FieldArray(I)) Then    'actual is null, use last scheduled max
        Maximum1 = currentVal
        Exit Function
      End If
    Else   
      If IsNull(currentVal) Then    
        Maximum1 = currentVal
        Exit Function
      End If
    End If
  Next I