Summing Continuous Form and Multiple Forms (1 Viewer)

[MajP]

Here is another useful example.
I made a function to get the additive score. This could be used in place of the more complex query.

Public Sub test()
  Debug.Print GetAdditiveScore(1, 10)
 
End Sub

Public Function GetAdditiveScore(AddID As Variant, AddValue As Variant) As Integer
  If Not IsNull(AddValue) And Not IsNull(AddID) Then
    Dim strWhere As String
    strWhere = "AdditiveID = " & AddID & " AND " & AddValue & " >= RangeStart AND " & AddValue & " < RangeEnd"
    Debug.Print strWhere
    GetAdditiveScore = Nz(DLookup("score", "additiveRanges_T", strWhere), 0)
  End If
End Function

I can test this function and verify the somewhat complex criteria and see if I get the correct result.

Public Sub test()
  Debug.Print GetAdditiveScore(1, 10)
End Sub

In the immediate window

AdditiveID = 1 AND 10 >= RangeStart AND 10 < RangeEnd
3

So for urgency and a value of 10 I get a score of 3 which is correct because it is between 6 and 12. Debug.print is the most useful thing to debug your code.

 

[Weekleyba]

I finally got most things to work. Still plugging away.
I will definitely work through your example. So much great stuff to learn here.
I spent a little time looking into Debug.Print. I never know how that worked with the Immediate window. Very helpful!
I also didn't know you need to hit F5 to get it to execute. Ha.... just learning here.
I need to play with that some more and commit that to memory.
Thanks for the all the help/teaching.
I'm sure I'll be back once I have more time to on Sunday.

 

[MajP]

Do your self a favor and read this

Debugging VBA

www.cpearson.com

It is just a starter, but you will save hours and hours if you learn the debuging tools and tricks. There is more than just debug.print to help you.