How to convert string number to numeric

Question

I have a list of dollar amounts that get totaled over a cfloop and query:

<cfset FileCount = 0>
<cfset FileDollar = 0>

<cfloop query="TxtQuery">
<cfset FileDollar = #FileDollar# + "#Payment_Amt#">
</cfloop>

Here is the data (input in first column under payment_amt, running total under FileDollar column):

Payment_amt(TEXT) FileDollar
14645791.820 14645791.82
5302608003.600 5317253795.42
405642.240 5317659437.66
11354914264.600 6672573702.3
1532528.280 16674106230.5
1532528.280 16675638758.8
1532528.280 16677171287.1
1532528.280 16678703815.4
16470800280.000 33149504095.4

It appears that once it hits the input payment_amt of 11354914264.600, the FileDollar goes from two decimal positions to one.
Is there any way to convert the text field to numeric before it gets totaled?

Libby_H · Accepted Answer

Thanks for the help. I eliminated the cfloop to add together the payment_amt fields, and replaced it with a Q of Q (like you recommended) using a cast statement.

<cfquery name="NEWQuery" dbtype="query">
select SUM(CAST(Payment_amt AS decimal)) AS TOTAL_AMT
from TxtQuery
</cfquery>

The TOTAL_AMT field now has the correct total, and is in decimal format.

Thanks again for your assistance!

Dan_Bracuk · Answer

Cold fusion has a cast function that works with Q ofQ.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded