Decimal Problem
in [Reports]
|
From: Avaya on 5 Jul 2008 02:04 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.
From: Allen Browne on 5 Jul 2008 04:48 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Avaya" <Avaya(a)discussions.microsoft.com> wrote in message news:B3EEA402-505F-4C41-81A8-AC3EC03642A9(a)microsoft.com... > 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]))
From: John Spencer on 5 Jul 2008 06:30 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 wrote: > 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. > > > > > > > > > > > > > > >
From: Avaya on 5 Jul 2008 10:44 Thank you, John. Problem solved. "John Spencer" wrote: > 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 wrote: > > 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. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
From: Avaya on 5 Jul 2008 18:28 Thank you Allen for replying to my post. I used the iif() and now it displayed the numbers correctly. "Allen Browne" wrote: > 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 > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > "Avaya" <Avaya(a)discussions.microsoft.com> wrote in message > news:B3EEA402-505F-4C41-81A8-AC3EC03642A9(a)microsoft.com... > > 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])) > >
|
Pages: 1 Prev: Case statement Problem not resolved Next: Graph won't display Zeroes |