Hi Peter ----
In your SQL statement, change the IIF False defaults from 0 to 0.00. This is what is causing the rounding; if the first record in the SQL statement creates a zero (0) value, the field is created with no decimal places.
>I have a report that has two - almost identical - fields. The data for the report is gathered by a large SQL statement and stored in a cursor. Both fields are SUMS of field in a table based off of a starting date. The ONLY difference - field #1 is data from a date in the middle of the month, field #2 is data from the first of the month.
>When I print the two fields on the form, field #2 (the "more" data) prints perfectly (ex: $7.50)... field #1 (the "less" data) prints $8.00. More research shows that their is DEFINATELY no additional records for either SUM that causes the descrepency. In addition, any "client" on this report, the "change" is rounded up for field #1.
>
>I have gone as far as copying field #2, pasting the copy on the form and editing the field name to verify their isn't any "hidden" mathematics or other settings causing the difference.
>
>
>
>
>Select client.firmid,client.clientnum,client.clientname,log.logdate,;
> sum(iif((code.codetype=="Billings" or ;
> code.codetype="WIP Adjustment" or ;
> code.codetype="Time" or ;
> code.codetype="WIP Start Bal") ;
> and log.logdate
> iif(code.codetype=="Billings",log.rate*-1,log.rate),0)) as BegBal,;
> sum(iif((code.codetype="Time" or code.codetype="WIP Start Bal" or ;
> code.codetype="Expense")and log.logdate>=gStartDate,log.rate,0)) ;
> as Charged, ;
> sum(iif((code.codetype="Time" or code.codetype="WIP Start Bal" or ;
> code.codetype="Expense")and log.logdate>=gYearBeg,log.rate,0)) ;
> as ChargedYTD, ;
> sum(iif(code.codetype="WIP Adjustment" and ;
> log.logdate>=gStartDate,log.rate,0)) as Adjusted, ;
> sum(iif(code.codetype="WIP Adjustment" and ;
> log.logdate>=gYearBeg,log.rate,0)) as AdjustedYTD, ;
> sum(iif(code.codetype=="Billings" and ;
> log.logdate>=gStartDate,log.rate,0)) as Billed, ;
> sum(iif(code.codetype=="Billings" and ;
> log.logdate>=gYearBeg,log.rate,0))as BilledYTD ;
> from Timepro!client inner join timepro!log on client.clientid = log.clientid ;
> inner join timepro!code on log.codeid = code.codeid ;
> where client.firmid=gFirmid and log.logdate<=gEndDate;
> into cursor cursWIPsum ;
> group by client.clientname ;
> order by client.clientnameHere is the SQL for reference:
>
>
>Any other suggestions?
------------------------------------------------
John Koziol, ex-MVP, ex-MS, ex-FoxTeam. Just call me "X"
"When the going gets weird, the weird turn pro" - Hunter Thompson (Gonzo) RIP 2/19/05