Truncating Calculated Text Box Result

[mikeTTI]

Hi All,

I have a calculated text box that displays a currency value to 2 decimal places.

I need to make this truncate (e.g. $87.679 = $87.67)

Does anyone know how?

The only thing I can think of is to -0.005 from the text box result. This would always make it round down, which acheives the truncation I need.

But! As I understand it, access handles decimal fractions ending in 5 so that half round up and half round down.

So if I try and force my numbers to rounddown, by substracting 0.005 from them, I will encounter a problem when the decimal fraction ends in 0, half of them will uneccesarilly round down the the next digit.

Does anyone have a solution for this?

Mike

 

[KeithG]

change the data format of the text box to display only 2 decimals. You have to set it as a standard number also.

 

[mikeTTI]

Thanks for the suggestion Keith. I tried it but the numbers are still rounding, so for example 200.237 is still rounding to 200.24

Does anyone have a way of truncating decimals?

To explain a bit more about this problem.

The database stores levy payments from customer created invoices.

For legal reasons I have to directly store the levy payment amounts.

For legal reasons, levy payments must always be rounded down to two decimals.

I use an unbound text box to claculate what a payment should be.

If the payment made is correct the user ticks a checkbox on the form and the amount in the unbound text box is copied across to the payment field. This cuts data entry time by around 66%, as most payment amounts are correct.

But I can't get the thing to truncate, hope someone can help

 

[missinglinq]

Are your calculations such that your value needing truncating is always going to have a decimal point in it? If so, I think you've actually stated what you need to do, which is to truncate your value to 2 decimal places, i.e. take away any digits after two to the right of the decimal point!

Where CalculatedValue is the value to be truncated and TruncatedVal is the value after truncation.

Me.TruncatedVal = Left(Me.CalculatedValue, InStr(Me.CalculatedValue, ".") + 2)

Or, if you always want to show 2 decimal places, even if the final answer wouldn't have two, you could use

Me.TruncatedVal = Format(Left(Me.CalculatedValue, InStr(Me.CalculatedValue, ".") + 2), "0.00")

As I said, this code is dependent on the CalculatedValue always having a decimal point in it. If there are cases where this may not be true, a couple more lines of code would be needed to cover this case. Let me know.

Linq

 

[mikeTTI]

Thanks Linq,

I have used your idea in a Public Function in my database which seems to work well.

It is Pasted below for anyone who is interested: -

Public Function RndDwn(DecPl As Integer, RawNum As String)
If InStr(1, RawNum, ".") = 0 Then
RndDwn = RawNum
Else
RndDwn = FormatCurrency((Left(RawNum, InStr(1, RawNum, ".") + DecPl)), 2, vbTrue, vbTrue, vbTrue)
End If

End Function

You specify the raw number and decimal places required, and the function does the rest.

The function counts the number of characters to the decimal point then adds the number of decimal places specified and uses the total as the number if characters in the Left function.

If the number doesn't containa decimal point it leaves it unchanged.

I have set it up to convert to currency in this instance, but any format could be used. Specifiying the format seems quite important if you wish to use the function result in any mathmatical calculation.

I quite enjoyed trying this out.

 

[missinglinq]

Glad you got it working for you. Nice job of adapting it to a function! Actually, I tested the results in calculations and it appears to work just fine as is. Access is so fickle in what it accepts and doesn't accept, but erring on the side of caution will seldom get you in trouble!

 

[mikeTTI]

The silly thing is in Excel there is a function called Rounddown, which will round any number down to a specified number of decimal places. (there is also a function called roundup).

Why these functions are not part of access escapes me.

I tried adding in the MS Excel and OLE reference libraries, but still no luck.

 

[missinglinq]

Well, Excel was designed for numbers crunching, while Access wasn't! MS Paint runs rings around Access in handling graphics; same story! My apps don't involve any heavy numbers crunching, so I only use Excel for, well, Excel type things! But while I don't know how, I'm fairly sure that there is a way to include a library and bring Excel functions to Access. Maybe someone else will wander by that knows how!

 

[mikeTTI]

Fair comment re number crunching, but I would have though some simple unidirectional rounding was a fairly commonly used tool.

I did try adding the MS Excel reference library, but tried a number of excel functions and they weren't recognised.

Am looking into this further, but would appreciate input from someone in the know.

Cheers

Mike

 

[missinglinq]

Here's a link to the horse's mouth (aka Micro$oft) that shows how to do it! Seems an awful lot of work for this, but maybe if the stakes were higher!

http://support.microsoft.com/kb/198571