Anyone know why "99.5 Mod 10" would return 0? I'm using Access VBA 2007 and Mod doesn't seem to be working right.

Data types being used are currency although I did try changing the variable data type to see if it would make a difference.

Try the code below like this - GetTransactionAmount(101.5) - and see what you get. It keeps returning the amount passed to it because the Mod operator doesn't seem to be returning correct values.

Any ideas?

Public Function GetTransactionAmount(TransAmt As Currency) As Currency

'this function is used to extract the actual amount withdrawn
'from an ATM machine even when a surcharge was applied to the whole transaction amount.
'ASSUMPTIONS:
' -ATM surcharges will never exceed $9.00 in the dataset the function is being used in
' -ATM withdrawal amounts will always be divisible by 10
' -ATM surcharges are only charged in increments of $0.25 (eg. an ATM will not charge a fee of $2.99)
' -All ATM machines only dispense whole dollar amounts divisible by 10.

Dim cSurchgTestAmt(100) As Double
Dim cNewAmt As Double
Dim i As Integer, x As Double, y As Double

x = 2.25
cSurchgTestAmt(i) = 4.25

For i = 0 To 100
cSurchgTestAmt(i) = x - 0.25
cNewAmt = TransAmt - cSurchgTestAmt(i)
If cNewAmt Mod 10 = 0 Then
GetTransactionAmount = TransAmt - cSurchgTestAmt(i)
Exit Function
End If
x = cSurchgTestAmt(i)
Next i

MsgBox "The 'GetTransactionAmount' function could not extract a value.", vbCritical, "Currency Extraction Error"

End Function

Yes ... because the Mod operator will round its operands prior to performing the operation, then returns only whole numbers ... (Byte, Integer, or Long)

So ...

99.5 Mod 10 ===> Round(99.5,0) Mod Round(10,0) ===> 100 Mod 10 = 0

....

The help article on Mod explains this very well ...

Because it rounds. Came up here:

http://www.access-programmers.co.uk/forums/showthread.php?t=152672

Thanks to both of you. pbaldy, I did read that post you linked me to prior to asking this question, but perhaps I read too fast.

Same w/the help article. I read it prior to asking this but totally missed the rounding part. I feel stupid now. :o

So if someone could point me in the right direction for code that tests if something is divisible by 10, please advise. What I'm trying to do with the code I posted is extract the actual ATM withdrawal amount if there's a fee attached. I thought that I could loop through in .25 increments and test whether the result is divisible by 10 would work by using the mod operator, but it's producing bad results b/c it rounds. If you could point me in the right direction to accomplish this goal, I'd appreciate it!

Example:

Total transaction amt= $101.75

My function GetTransactionAmount($101.75) should equal $100.00.

Thanks everyone.

If you want the decimal remainder ... here's a couple of ways to get it ...

CDbl(TimeValue(CDate(99.5/10))) = 0.95

99.5/10 - Int(99.5/10) = 0.95

I use the first option when my expression is more complex and I don't what to have to repeat it ...

To truncate to the nearest multiple of 10 ... I use a UDF I call fNearest()


Public Function fNearest(dblValue As Double, _
Optional dblInterval As Double = 1, _
Optional blUseCeiling As Boolean = True) As Double
'Returns the ceiling or floor of the passed value to the nearest
'interval. blUseCeiling of False will coerce to the interval value
'less than the passed value, blUseCeiling of TRUE (Default) will
'coerce to the interval value greater than the value passed.

If dblInterval <> 0 Then
If blUseCeiling = True Then
fNearest = -Int(-dblValue / dblInterval) * dblInterval
Else
fNearest = Int(dblValue / dblInterval) * dblInterval
End If
Else
fNearest = 0
End If

End Function


fNearest(101.75, 10, False) = 100

It's the thin air up there. :p

Awesome, it worked like a charm (the first one).

Thanks so much for your time and help.

RG,

... You can check with Paul and I ... we got you covered buddy!!!

RG,

... You can check with Paul and I ... we got you covered buddy!!!Thanks Brent. Saw your posts after I posted. I'm am now wiser. :D:D:p

XLE ...

I would suggest you use the fNearest() function ... its a great one to have in your applicaton! ... and its a faster that looping as you are doing.

After all you really don't care about the remainder ... you just need the cash amount given by the ATM correct?

....

Or ... if you want ... you don't even need an User Defined Function for the single purpose, just use the expression ...

Int(dblValue / dblInterval) * dblInterval

Int(101.75 / 10) * 10 = 100