Board index » delphi » Forcing SQL Sum(value) to display 2 decimal places

Reid Roman

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Forcing SQL Sum(value) to display 2 decimal places

I have noticed on a report that when a value is summed via SQL, if the
value's summed total is not an even dollar amount, (ex: $12.33) the
result will display as $12.33.

If it is an even amount (ex: $12.00) it will display as $12  .....

Is there a way to force to result of Sum() to ALWAYS display a 2 digit
decimal or float value even when it will be ".00" ??

Here is the SQL for that TQuery: (Using Delphi  v3.0)
---------------------------------------------------------
SELECT E.VendorName, E.AmountPaid, E.InvoiceDate, E.Category,
Sum(E.AmountPaid) Vendor_Total
FROM Expense E
WHERE InvoiceDate BETWEEN  (:pStartDate)  AND  (:pEndDate)
AND Category = (:pCategory)
OR VendorName = (:pVendorname)
GROUP BY  VendorName, InvoiceDate, AmountPaid, Category;
---------------------------------------------------------

Any help would be greatly appreciated!

Thanks

Rkr
--

                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. Delphi Programmer / Analyst             .
. TVisualBasic:=class(None);              .
. May the Source be With You              .
-------------------------------------------
. Auto-By-Tel (http://www.autobytel.com)  .
. Irvine, CA U.S.A                        .
. E-Mail : rkroman (at) pacbell (dot) net .
. or reidr (at) autobytel (dot) com       .
-------------------------------------------

 
clar

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Forcing SQL Sum(value) to display 2 decimal places


It may not be appropriate to change the way SQL handles the
calculations internally.  Consider how you display the resultant
rows/cells.

By introducing a

   "TField.GetText"

Event handler you can easily force any attached control to
display two digits with a

"aDBControl.Text :=
  FormatFloat('$0.00',FieldByName('Vendor_Total').AsFloat);"

Hope the info helps.

Regards Railton....

--

Quote
Reid Roman wrote:

> I have noticed on a report that when a value is summed via SQL, if the
> value's summed total is not an even dollar amount, (ex: $12.33) the
> result will display as $12.33.

> If it is an even amount (ex: $12.00) it will display as $12  .....

> Is there a way to force to result of Sum() to ALWAYS display a 2 digit
> decimal or float value even when it will be ".00" ??

> Here is the SQL for that TQuery: (Using Delphi  v3.0)
> ---------------------------------------------------------
> SELECT E.VendorName, E.AmountPaid, E.InvoiceDate, E.Category,
> Sum(E.AmountPaid) Vendor_Total
> FROM Expense E
> WHERE InvoiceDate BETWEEN  (:pStartDate)  AND  (:pEndDate)
> AND Category = (:pCategory)
> OR VendorName = (:pVendorname)
> GROUP BY  VendorName, InvoiceDate, AmountPaid, Category;
> ---------------------------------------------------------

> Any help would be greatly appreciated!

> Thanks

> Rkr
> --

>                    \|||/
>                    /'^'\
>                   ( 0 0 )
> --------------oOOO--(_)--OOOo--------------
> . Reid Roman                              .
> . Delphi Programmer / Analyst             .
> . TVisualBasic:=class(None);              .
> . May the Source be With You              .
> -------------------------------------------
> . Auto-By-Tel (http://www.autobytel.com)  .
> . Irvine, CA U.S.A                        .
> . E-Mail : rkroman (at) pacbell (dot) net .
> . or reidr (at) autobytel (dot) com       .
> -------------------------------------------

Ihor A. Shtank

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Forcing SQL Sum(value) to display 2 decimal places


Quote
> If it is an even amount (ex: $12.00) it will display as $12  .....

Change in corresponding field EditMask to something like that $0.00
--

Ihor A. Shtanko
i...@grant.kharkov.ua

ferenc.vil..

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Forcing SQL Sum(value) to display 2 decimal places


In article <3492CF0B.FB5BD...@pacbell.net>,
  or, re...@autobytel.com wrote:

Quote

> I have noticed on a report that when a value is summed via SQL, if the
> value's summed total is not an even dollar amount, (ex: $12.33) the
> result will display as $12.33.

> If it is an even amount (ex: $12.00) it will display as $12  .....

> Is there a way to force to result of Sum() to ALWAYS display a 2 digit
> decimal or float value even when it will be ".00" ??

> Here is the SQL for that TQuery: (Using Delphi  v3.0)
> ---------------------------------------------------------
> SELECT E.VendorName, E.AmountPaid, E.InvoiceDate, E.Category,
> Sum(E.AmountPaid) Vendor_Total
> FROM Expense E
> WHERE InvoiceDate BETWEEN  (:pStartDate)  AND  (:pEndDate)
> AND Category = (:pCategory)
> OR VendorName = (:pVendorname)
> GROUP BY  VendorName, InvoiceDate, AmountPaid, Category;
> ---------------------------------------------------------

> Any help would be greatly appreciated!

> Thanks

> Rkr
> --

>                    \|||/
>                    /'^'\
>                   ( 0 0 )
> --------------oOOO--(_)--OOOo--------------
> . Reid Roman                                 .
> . Delphi Programmer / Analyst                .
> . TVisualBasic:=class(None);                 .
> . May the Source be With You                 .
> -------------------------------------------
> . Auto-By-Tel (http://www.autobytel.com)  .
> . Irvine, CA U.S.A                           .
> . E-Mail : rkroman (at) pacbell (dot) net .
> . or reidr (at) autobytel (dot) com          .
> -------------------------------------------

Use the Field Editor, then you can set the Display Mask (I hope so). Mask
'$0.00'

Feri

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Reid Roma

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Forcing SQL Sum(value) to display 2 decimal places


Quote
Railton Frith wrote:

> It may not be appropriate to change the way SQL handles the
> calculations internally.  Consider how you display the resultant
> rows/cells.

> By introducing a

>    "TField.GetText"

> Event handler you can easily force any attached control to
> display two digits with a

> "aDBControl.Text :=
>   FormatFloat('$0.00',FieldByName('Vendor_Total').AsFloat);"

> Hope the info helps.

Thanks for the reply..

I should of mentioned that the problem was occuring in Quick Report with
the use of a TExpression Label.

I was able to solve it by using your advice using "FormatFloat()" in
it's
Print method.

{-------- Make sure the value is displayed like: $1,000.00
--------------------}
procedure TReportGeneratorForm.QRExprGrandSummaryPrint(sender: TObject;
  var Value: String);
var
   vFloat : extended;
begin
   vFloat := StrToFloat(Value);
   Value := FormatFloat('$,##0.00',VFloat);
end;

Thanks!

Rkr

--
                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. Delphi Programmer / Analyst             .
. TVisualBasic:=class(None);              .
. May the Source be With You              .
-------------------------------------------
. Auto-By-Tel (http://www.autobytel.com)  .
. Irvine, CA U.S.A                        .
. E-Mail : rkroman (at) pacbell (dot) net .
. or reidr (at) autobytel (dot) com       .
-------------------------------------------

Other Threads

1. Specifying decimal places for field display

2. Displaying dBase floats with 2 decimal places in TDBGrid

3. Restrict display to 2 decimal places

4. Decimal value displayed incorrect in Delphi 6!!!

5. Decimal value displayed incorrect in Delphi 6!!!

6. Help displaying Decimal values in a DBEdit control

7. SQL SUM - Query Value out of Bounds

8. value of sql sum() - Help!

9. SQL: Value of SUM-QUERY

10. SQL: Value of SUM-QUERY