# Function for just the decimal portion of currency

1. Expert
Windows 10 Access 2021
Join Date
Jun 2022
Location
Colombia
Posts
565

## Function for just the decimal portion of currency

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:

Code:
`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.

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

3. Expert
Windows 10 Access 2021
Join Date
Jun 2022
Location
Colombia
Posts
565
@ 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().

4. Competent Performer
Windows 8 Access 2016
Join Date
Dec 2022
Posts
223
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:
Code:
```            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
Code:
```            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
Code:
```            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

5. Expert
Windows 10 Access 2021
Join Date
Jun 2022
Location
Colombia
Posts
565
@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.

6. 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))

7. Expert
Windows 10 Access 2021
Join Date
Jun 2022
Location
Colombia
Posts
565

## Unless I'm missing something here...

Originally Posted by 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))
One Abs() and Fix() should do the trick.

8. Competent Performer
Windows 8 Access 2016
Join Date
Dec 2022
Posts
223
Originally Posted by twgonder
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums