Using the tonumber command

[maxxam]

I'm a beginner with CR8.5 and I'm trying to learn the TONUMBER command and how to use it in a simple report that I created. The data in some of the key fields I need in my report are coming into my report as a text fields and as a result I cannot use any of the calculation options (Grand Total, etc.) in CR8.5 I've read and understand that I need to incorporate the TONUMBER statement into my query but I'm not familiar with the syntax to use. Can anyone tell me where and how to input the command in the SQL script below to get the 'billed, paid' fields processed as numbers rather then text.

Thanks,

Rudy


SELECT DISTINCT
RS.`FILE_NAME`, RS.`CLAIM_DATE`, RS.`WARRANT1`, RS.`CLIENTNAME`, RS.`CLNTNUMBER`, RS.`GROUP`, RS.`PERIODFROM`, RS.`PERIODTO`, RS.`BILL`, RS.`RATE`, RS.`BILLED`, RS.`PAID`, RS.`EOB`, RS.`CONTRACT`, RS.`UBILLED`, RS.`UPAID`, RS.`BILL_FILE`
FROM
`RS` RS
ORDER BY
RS.`CLAIM_DATE` ASC

 

[synapsevampire]

You can't modify the query in CR 8.5, you'd want to use a formula or a SQL Expression.

I'll show you the formula method as you didn't share the database type and version:

Select Insert->Field Object->Right click formulas and select New.

Give it a name, such as BILLEDnum, and use the following:

val({RS.`BILLED})

Now use this formula field in your report rather than the database field.

-k

 

[maxxam]

Thanks for your help... that was too easy!

maxxam

 

[synapsevampire]

I can complicate it if you'd like?

-k

 

[maxxam]

No thanks but I do have another question. Can I write a formula in Crystal that will look at the results of a record to compare AmountBilled to AmountPaid and only display those records that have a discrepancy between them? If so, how?

Thanks again!

 

[chelseatech]

Use a record selection formula of
{Table.AmountBilled} <> {Table.AmountPaid}

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[maxxam]

Thanks .. that worked well but I have a problem. What happens if one of the records does not have any data in the PAID field (i.e. 100.00 Billed - blank Paid)? I ran the report and the results seemed OK ... meaning the agument seemed to work and displayed what was requested but after further review there were several records that did not appear and all of these &quot;missing&quot; records have nothing in the PAID field. Any suggestions on what to do to include them on my report?

 

[lbass]

Either go to report options and check &quot;convert nulls to default&quot; or write a formula to use in place of {table.amountpaid}:

if isnull({table.amountpaid}) then 0 else
{table.amountpaid}

-LB

 

[maxxam]

Thansk that worked.

 

[maxxam]

One more issue ...

I used the following expression to eliminate certain records from my query and now they want more!!!

{Table.AmountBilled} <> {Table.AmountPaid}

How can I make this formula only display items that are greater than one cent $.01 in variance?

Maxxam

 

[dgillz]

abs({Table.AmountBilled} - {Table.AmountPaid})>.01

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com