Decimal Problem

[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.

 

[Allen Browne]

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

 

[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
'====================================================

 

[Avaya]

Thank you, John.

Problem solved.

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.

 

[Avaya]

Thank you Allen for replying to my post.

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

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.

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]))