complex calculation in Access 1

[ragnarok75]

I need help with a calculation in Access. I need to take the value from a field on a form, divide it by 4, round the result to a multiple of 4, multiply the result by 40 and then divide by 12. The result needs to be outputted to another field on the form. Any ideas?

 

[CautionMP]

ragnarok75,
Without putting much thought into it, place the following function in the code module of your form, and then in the [tt]Control Source[/tt] of another field on the form.

=ComplexCalculation([field on a form])

Function ComplexCalculation(InputNumber As Double) As Double
Dim dblOutput As Double
'Since we round to 4 use integer division
dblOutput = InputNumber \ 4
'Round the number 1/2 split
Select Case dblOutput Mod 4
  'Round down
  Case 1
    dblOutput = dblOutput - (dblOutput Mod 4)
  'round up
  Case 2, 3
    dblOutput = dblOutput + (dblOutput Mod 4)
End Select
dblOutput = dblOutput * 40
dblOutput = dblOutput / 12
ComplexCalculation = dblOutput
End Function

(GMT-07:00) Mountain Time (US & Canada)

 

[MichaelRed]

or simplified ...

Function basComplexCalculation(InputNumber As Double) As Double

    Dim dblOutput As Double
    Dim intRndVal As Integer
    'Since we round to 4 use integer division

    dblOutput = InputNumber \ 4
    intRndVal = dblOutput Mod 4

    Select Case intRndVal
        'Round down
        Case 1
            dblOutput = dblOutput - intRndVal
        'round up
        Case 2, 3
            dblOutput = dblOutput + intRndVal
    End Select

    dblOutput = dblOutput * 40 / 12

    ComplexCalculation = dblOutput

End Function

MichaelRed

 

[TheAceMan1]

How are ya csunsun . . .

. . . and this:

[blue]   Me!ResultTxtboxNam = Cdbl(FieldName)/CDbl(12)[/blue]

See Ya! . . . . . .

 

[ragnarok75]

Thanks for the suggestions. I have modified this to instead of by 4 it is by 2. Here is my problem - example:

13 enters the formula
13/2 = 6.5 and therefore should round to 7
However 6.5 is rounded to 6 which results in the incorrect final answer.

Any ideas?

 

[CautionMP]

ragnarok75,
You could use the [tt]Round()[/tt] function but it does the classic 5/6 split so 6.5 would be 6. Here is an approach:

Dim intDecimal As Integer
intDecimal = (13/2 - Int(13/2)) * 100
If intDecimal >= 50 Then
  [i]OutputValue[/i] = Int(13/2) + 1
End If

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[ragnarok75]

I need to always round up. 6.1 needs to round to 7 - I know you can do this in Excel, but what about Access?

 

[CautionMP]

How would you do it in Excel?

(GMT-07:00) Mountain Time (US & Canada)

 

[ragnarok75]

By using the Roundup function

ROUNDUP(number,num_digits)

 

[CautionMP]

ragnarok75,
Using the concepts in this thread we can replicate the Excel [tt]RoundUp()[/tt] function.

Public Function MyRoundUp(number As Double, num_digits As Integer) As Double
Dim dblOutput As Double
Dim intRndVal As Integer
'Shift decimal num_digits
dblOutput = number * (10 ^ (num_digits + 1))
'Remove decimal
dblOutput = Fix(dblOutput)
'Check if last digit needs to be rounded
intRndVal = dblOutput Mod 10
If intRndVal <> 0 Then
  'it does so round
  dblOutput = dblOutput + (10 - intRndVal)
End If
'Shift the decimal back num_digits
MyRoundUp = dblOutput / (10 ^ (num_digits + 1))
End Function

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[SuryaF]

Or, a bit simplified:

Public Function MyRoundUp(number As Double, num_digits As Integer) As Double

MyRoundUp = (Int(number * (10 ^ num_digits)) + 1) / (10 ^ num_digits)

End Function

 

[CautionMP]

SurysF,
[tt]MyRoundUp(12,1) = 12.1?[/tt]

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[SuryaF]

ok, let's try this:

Public Function MyRoundUp(number As Double, num_digits As Integer) As Double

MyRoundUp = (Int(number * (10 ^ num_digits)) + 1 * Sgn(Abs(number) * (10 ^ (num_digits + 1)) Mod 10)) / (10 ^ num_digits)

End Function

this should work for negatives also.

 

[ragnarok75]

All of these suggestions round 6.25 to 6.3 instead of 7 - I need to be left with a non-decimal number.

I am also finding that access is sometimes truncating the decimal point. Code: textbox.value / 4 - If the textbox value is 33; 33/4 equals 8.25 which I need rounded to 9, however Access sometimes will return 8 instead of 8.25 which of course gives the wrong answer.

 

[Ed2020]

Just add a reference to the Excel Object Library and then use Excel's roundup function:

=Excel.WorksheetFunction.Roundup(numToRound, 0)

Ed Metcalfe.

Please do not feed the trolls.....

 

[Golom]

What you really want is a CEILING function

X = 6.1
X = Int(IIF(Int(X)<X,X+1,X))

 

[SuryaF]

The suggested MyRoundUp function was for a variable number of digits. Now if you want to round without decimals use Golom's function or, if you want something more arithmetic
y = Int(x) + Sgn(x - Int(x)) * 1

 

[ragnarok75]

Thanks for everyone's input. I went with Ed2020's answer - It was quick and simple and so far seems to give me the correct result. I will continue testing and again, thanks to everyone for thier help!!

 

[MichaelRed]

Quick ... from the implementation standpoint, but otherwise you are invoking considerable overhead (loading an additional library - of considerable size(, This will slow the overall process down somewhat and may cause a dramatic change in performance depending on other factors like how much (free) memory and disc space is available on the system(s) hte app is running on.

Simple ... is also only from the immediate perspecitve, as (againg depending on other factors) you may spend some considerable time getting the reference on ALL workstations which instantiate the app,



MichaelRed

 

[Ed2020]

I haven't noticed any performance issues having added the Excel object library. Of course YMMV....

Ed Metcalfe.

Please do not feed the trolls.....