Function for just the decimal portion of currency

[twgonder]

Good morning!, and today's question should be quite simple.
I'm working on a function to get just the fractional part of a field of currency as a whole number.

In the past, in assembly I've used an approach something like:

n2 = (n1 - int(n1)) * 10000   'n2 is just the fractional part of n1

This is going to be in a function, run against hundreds of thousands of records in queries and VBA.

Is there a simpler approach in Access that might be faster (and might not need the function)?


Has anyone had the int() do strange stuff that might goof this up if I use the first approach?

Note:
I just thought of maybe making it a calculated value in the table (with the same equation), but my intuition says this might not be a good approach.

[orange]

Seems reasonable, but are all currency values positive? May need abs(n1).

[twgonder]

@ Orange, post #2 No, some are negative, which made me think about rounding with Int(), but since there are four decimal points, I was afraid of what Int() might do to the negative whole number part. In my past systems, Int() didn't round, just gave the whole number, positive or negative. I remember there is another function in VBA, but I can't recall (hard to search when you don't remember what you're looking for) what it is and how it differes from Int().

[Edgar]

I tested a bunch of methods and got to the conclusion that these are the fastest, but I'd probably choose the second in the list, I like working with Long instead of Integer:

            n2= n1 - CInt(n1)
            n2= n1 - CLng(n1)
            n2= CDbl(n1) - CInt(n1)
            n2= CDbl(n1) - CLng(n1)

I tried with this string manipulation method as well and it takes about 3 times longer

            If n1 < 0 Then
                n2 = ("-0." & Split(FormatNumber(n1, 8), ".")(1)) * 1
            Else
                n2 = ("0." & Split(FormatNumber(n1, 8), ".")(1)) * 1
            End If

It's worth mentioning that I got a slightly faster result with these two, but if there are negatives in the dataset, it's best to not use them

            n2 = n1 - Int(n1)
            n2 = n1 - Fix(n1)

Attached is a sample with the 7 methods, but it's clear that string manipulation should not be used. This is the result:
method 1 0.024389
method 2 0.024188
method 3 0.024928
method 4 0.024898
method 5 0.083601 <-- string manipulation
method 6 0.024453
method 7 0.023787

[twgonder]

@Edgar, post# 4 Thanks again, you're are always so thorough in your responses.
It's hard to do a benchmark in Windows, because you never know what Windows is doing in the background that can contaminate the results, unless you know a way to pause all those, and I don't.

The math results are all so close that we could consider them equal for the above reason.
The Fix() is what I was thinking of.

Having written string array functions in assembly, which can often be hundreds or thousands of instrucions
I'm surprised the string manipulation approach was only three times slower.

I'll dig into your .accdb when I finish a pressing legal task.

I'm still curious however, if anyone knows a reason NOT to do all this with a calculated field in the table. I've got lots of tables and code that would use this function, but it would be so much easier to have the field already in the hundreds of tables that need it.

[orange]

Are these applicable ??
'using absolute value to ignore sign
res = Abs(targetN) - Int(Abs(targetN))
OR
'using absolute value to ignore sign
res = Abs(targetN) - Fix(Abs(targetN))

[twgonder]

Are these applicable ??
'using absolute value to ignore sign
res = Abs(targetN) - Int(Abs(targetN))
OR
'using absolute value to ignore sign
res = Abs(targetN) - Fix(Abs(targetN))

One Abs() and Fix() should do the trick.

[Edgar]

I'm still curious however, if anyone knows a reason NOT to do all this with a calculated field in the table. I've got lots of tables and code that would use this function, but it would be so much easier to have the field already in the hundreds of tables that need it.

No idea, I never use calculated fields. So far, built-in features perform better than my VBA code too. But maybe you can test its performance with a large table with millions of records.