Decimal Points in a text box

[Dr Peter Klomp]

I have a text box in a MS Access report that calculates tax from the value of another text box in the same report. I want the tax calculation to show whole numbers only, unless there are cents. For example, either 75 or 75.25.
What should I put in the Property Sheet? I don't (think I) want to show the calculation as currency, because a $ sign shows up. I have tried a few options like selecting General Number, Standard, Auto decimal places, but not a lot of joy. Any help would be much appreciated. Thank you.

 

[June7]

What about 75.30 - do you want the 0?

 

[Dr Peter Klomp]

Yes, 75.3 should show as 75.30

 

[June7]

Well, that does complicate things. Do you need this value for use in subsequent calcs?

Why not just show the 2 zeros? Seems to me the inconsistency in decimals would be harder to read.

I don't think can do this with property settings. Would have to calculate a text string with an IIf() conditional.

 

[Dr Peter Klomp]

Yes. I need the tax calculation to be added into a total amount. The reason I don't want to show decimals unnecessarily is purely because of asthetics. When a client sees a bill for, say, $129, they won't fee as bad as seeing a bill for $129.00. Seems weird, but that is the psychology of it. Happy to use a text string with an IIf() if you can give me some pointers.

 

[June7]

So guess these amounts are not shown together as in column of a table.

=Format(fieldname, IIf(fieldname - Int(fieldname) > 0, "0.00", ""))

 

[The_Doc_Man]

Do it in two steps.

First, compute the number as xxxxx.xx (with the decimal places even if they are zeros).
Then check for the special case to suppress ".00" at the right of the resultant string.

thenumber = Format( thetotal, "########0.00" )
thenumber = IIF( Right( thenumber, 3 ) = ".00", Right( thenumber, 3 ) & "   ", thenumber )

You could build a function to do this, but if it is in a text box you can compute this during the OnCurrent event.

 

[Dr Peter Klomp]

Thanks, Guys
I took a bit of both of your answers, and OnLoad of the Report put this code:
If txtboxTax - Int(txtboxTax) > 0 Then
txtboxTax.DecimalPlaces = 2
Else: txtboxTax.DecimalPlaces = 0
Thank you again.

 

[June7]

Why use VBA when expression in textbox serves? Or even calc in query.

Can still use the actual field value in subsequent calcs.

 

[Gasman]

The reason I don't want to show decimals unnecessarily is purely because of asthetics. When a client sees a bill for, say, $129, they won't fee as bad as seeing a bill for $129.00. Seems weird, but that is the psychology of it.

Hmm, glad the banks and other companies do not feel that way.

 

[Gasman]

What should I put in the Property Sheet? I don't (think I) want to show the calculation as currency, because a $ sign shows up.

I am in the UK and I get my currency symbol, which is a £ ?

 

[Cotswold]

The reason I don't want to show decimals unnecessarily is purely because of asthetics. When a client sees a bill for, say, $129, they won't fee as bad as seeing a bill for $129.00. Seems weird, but that is the psychology of it.

Can't see why someone would think that $129 is less than $129.00 but you're the doctor.
Personally I see missing decimals and think is that a misprint or a mistake. I'd then check every detail on the invoice.

Why not adjust your prices so that you use the con-trick that some companies use because they think it will trick the simple people and use $128.99