Decimal Problem

A

Avaya

Hi
My report is based on the following expression in my query in one of the
text box:

Balance: Format$(nz(([sumofpoamt]-[paidnet]),[sumofpoamt]),"$0.00")

This works fine but when I added another text box to sum the total, Access
ignore the decimal number. For example:

8.00
9.00
6.25

Access display 23.00 instead of 23.25

This text box record source =sum(clng([balance]))

How can I get Access to display the correct calculation?

Thank you for your help.
 
J

John Spencer

Try using CCur instead of CLng.

Clng returns integers (no decimal portion).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
A

Avaya

Thank you, John.

Problem solved.


John Spencer said:
Try using CCur instead of CLng.

Clng returns integers (no decimal portion).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi
My report is based on the following expression in my query in one of the
text box:

Balance: Format$(nz(([sumofpoamt]-[paidnet]),[sumofpoamt]),"$0.00")

This works fine but when I added another text box to sum the total, Access
ignore the decimal number. For example:

8.00
9.00
6.25

Access display 23.00 instead of 23.25

This text box record source =sum(clng([balance]))

How can I get Access to display the correct calculation?

Thank you for your help.
 
A

Avaya

Thank you Allen for replying to my post.

I used the iif() and now it displayed the numbers correctly.

Allen Browne said:
The problem here is to do with data types.
Format() creates a string, which doesn't sum correctly.

Try something like this:
Balance: IIf((([sumofpoamt] Is Null, 0, [sumofpoamt])
- IIf([paidnet] Is Null, 0, [paidnet])

Explanation:
http://allenbrowne.com/QueryPerfIssue.html#Nz

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Avaya said:
Hi
My report is based on the following expression in my query in one of the
text box:

Balance: Format$(nz(([sumofpoamt]-[paidnet]),[sumofpoamt]),"$0.00")

This works fine but when I added another text box to sum the total, Access
ignore the decimal number. For example:

8.00
9.00
6.25

Access display 23.00 instead of 23.25

This text box record source =sum(clng([balance]))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top