2 Replies Latest reply: May 15, 2014 9:59 AM by Nick Osdale-Popa RSS

    As my Capt. in the Corps would say: ITABWODI?

    Nick Osdale-Popa

      [b]I[/b][/i]s [b]T[/b][/i]here [b]A[/b][/i] [b]B[/b][/i]etter [b]W[/b][/i]ay [b]O[/b][/i]f [b]D[/b][/i]oing [b]I[/b][/i]t?


      I finally had the opportunity to use the SetRuntimeParameter() Function today.


      For a monthly payroll report, we have to some times split up the weeks between months depending on when the paydate falls. The report shows 5 weeks, but the first two weeks may belong to one month and the last three may belong to the next month.


      Thus I have Hour Type formulas such as:

      [font="courier"]wk1ST + wk2ST + wk3ST + wk4ST + wk5ST 'Reg Time

      wk1OT + wk2OT + wk3OT + wk4OT + wk5OT 'Ovr Time

      wk1DT + wk2DT + wk3DT + wk4DT + wk5DT 'Dbl Time[/font][/quote]Prior to Ver8, this led me to having to edit the model A LOT, depending on which weeks needed to be totaled.


      I finally came up this idea. Set 5 numeric runtime parameters, (wk1...wk5), if the parameter equals 1, include the week, if it equals 0, do not include it. 


      Now my formulas look like this:

      [font="courier"]If(wk1, wk1ST, 0) +

      If(wk2, wk2ST, 0) +

      If(wk3, wk3ST, 0) +

      If(wk4, wk4ST, 0) +

      If(wk5, wk5ST, 0)


      If(wk1, wk1OT, 0) +

      If(wk2, wk2OT, 0) +

      If(wk3, wk3OT, 0) +

      If(wk4, wk4OT, 0) +

      If(wk5, wk5OT, 0)


      If(wk1, wk1DT, 0) +

      If(wk2, wk2DT, 0) +

      If(wk3, wk3DT, 0) +

      If(wk4, wk4DT, 0) +

      If(wk5, wk5DT, 0)[/font][/quote]This lets me be creative in which weeks I can add, I can pass parameters to wk1, wk3, & wk5, if I so desired.


      In my code, I have a form with the five weeks as check boxes and I pass their values to an array like this:


      [font="courier"]garWeeks(1) = "wk1=" & myform.chkbx1.value


      garWeeks(5) = "wk5=" & myform.chkbx5.value[/font][/quote]My code is a little more streamlined using a For..Next loop through the controls rather than hardcoding like that above.


      I then pass the array to to a routine that seperates the field (runtime parameter) and the value:


      [font="courier"]Sub SetParameters()

          Dim strItem As String

          Dim strField As String

          Dim strValue As String

          Dim intPos As Integer

          Dim idx As Integer

          Dim intCnt As Integer

          Dim retVal As Integer


          On Error Resume Next

          intCnt = UBound(garWeeks)

          If intCnt = 0 Then Exit Sub


          For idx = 1 To intCnt

              strItem = garWeeks(idx)

              intPos = InStr(strItem, "=")

              If intPos = 0 Then Exit Sub


              strField = Left(strItem, intPos - 1)

              strValue = Mid(strItem, intPos + 1)

              retVal = mapMonarch.SetRuntimeParameter(strField, strValue)

          Next idx


      End Sub[/font][/quote]Pretty nifty, eh?



      My question was[/i] going to be, how could I shorten this to only use 1 runtime parameter, however, I believe I came up with the solution:


      Send 1 Character parameter like this:

      parmWeeks: 11111


      parmWeeks: 10101



      My formulas would now look like such:


      [font="courier"]If(Substr(parmWeeks,1,1)="1", wk1ST, 0) +

      If(Substr(parmWeeks,2,1)="1", wk2ST, 0) +

      If(Substr(parmWeeks,3,1)="1", wk3ST, 0) +

      If(Substr(parmWeeks,4,1)="1", wk4ST, 0) +

      If(Substr(parmWeeks,5,1)="1", wk5ST, 0)


      etc...[/font][/quote]That's about it. Unless someone else has another idea on how to do it differently?


      Any suggestions?  

        • As my Capt. in the Corps would say: ITABWODI?
          Data Kruncher



          When I began reading your post, I thought that I had a different solution I use for a similar requirement. Our fiscal periods do not match calendar months, but for reporting purposes we refer to them with calendar names. So I thought this was where you were headed. Then I read your solution to your own question. Brilliant! Bloody brilliant... Well done.  :cool: 


          If I keep seeing binary-style solutions, you'll soon have me thinking about Dwords, MOVs, RETs and other horrible programming memories.    


          Now I'm wondering how/if one could apply this technique to other things (like filters? Benificial if even possible?). Need time to play. Should probably try it before even asking the question. Oh well... Thoughts?



          • As my Capt. in the Corps would say: ITABWODI?
            Nick Osdale-Popa

            Oh, yeah, I could definitely see applications of using Filters with this idea. Ideas started sparking in my head when I read that. Will have to run some tests. I would think AND type filters would be easy and with not much effort OR types too.