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

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

[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) = &quot;wk1=&quot; &amp; myform.chkbx1.value

:

garWeeks(5) = &quot;wk5=&quot; &amp; 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, &quot;=&quot;)

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

or

parmWeeks: 10101

etc:

My formulas would now look like such:

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

If(Substr(parmWeeks,2,1)=&quot;1&quot;, wk2ST, 0) +

If(Substr(parmWeeks,3,1)=&quot;1&quot;, wk3ST, 0) +

If(Substr(parmWeeks,4,1)=&quot;1&quot;, wk4ST, 0) +

If(Substr(parmWeeks,5,1)=&quot;1&quot;, 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?

Nick,

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?

Kruncher

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

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.