round only when there are decimals

[noe1818 via AccessMonster.com]

I would like a field to round to one decimal if a calculation produces
decimals. If the calculation comes to a whole number I want the field to show
no decimals. How can I do this?

 

[Jeff Boyce]

One approach might be to test whether the result of the calculation is equal
to the result converted to an integer (CInt()). This is something you could
do in a query that you use to "feed" a form.

Keep in mind that the way Access stores data in the fields of a table is not
necessarily the same as the formatted-variation of that value that Access
can display in a form or report.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Damon Heron]

Here is a sub that takes a number (mynum) and rounds it to 1 decimal, then
tests to see if it is integer. if so, then makes it whole.

Public Sub RoundorInt(mynum)
mynum = Format(mynum, "##,##0.0")
If CSng(mynum) = Int(CSng(mynum)) Then
mynum = Format(mynum, "##,##0")
End If
End Sub

Damon

 

[noe1818 via AccessMonster.com]

Two questions:
1) is "mynum" a vba word, or is just what you called my field? If it is a
code word, what do I need to put inorder for my text box to follow the code?
2) Can I just copy and paste your code into the activate section of my report,
or does it have to go somewhere specific?

 

[Damon Heron]

Oh, you didn't mention it was for a report. Forget my subroutine
(incidently, when you call a sub, from a form, the mynum is a variable that
you send to the sub)

Anyway, for a report, do the following:
create 3 textboxes (I'm calling them text0, text1, text2)

Text0's control source= the field from table or query
Text1's control source: =Format([Text0],"##,##0.0")
Text2's control source:
=IIf((CSng([text1])=Int(CSng([text1]))),Format([text0],"#,##0"),Format([text0],"#,##0.0"))
make Text0,Text1 invisible.

Text2 will display the correct format. There is probably a way to do this
with fewer textboxes, but by breaking
them up, it makes the expression easier to understand.

Damon