Roundoff Problem 1

[vree]

Need help due to roundoff error...

I have a query which is the recordsource for a report. In the OnActivate event of the report I want this to happen...

Look at column #6, if greater than 0, look at columns 1-5, determine the first column that is greater than zero, and add the value in column 6, to it.

Please help, if you need more info just let me know.

 

[PWise]

first make sure that text for column 6 is not bound
in OnActivate event
column6textboxname.rowsource=iif(QueryCol6>0,iif(QueryCol1>0,QueryCol1,iif(QueryCol2>0,QueryCol2,iif(QueryCol3>0,QueryCol3,iif(QueryCol4>0,QueryCol4,iif(QueryCol5>0,QueryCol5,WhatYouWantItToBeIfNoneAreGreaterThenSix))))))

 

[PWise]

that should be column6textboxname.controlsource=iif(....

 

[vree]

Not quite...after determining the difference amount in column 6, i want to look at every column, (looP), then first one that is >o, add the amount in column 6 to that column. (done)

So if column 6 value= 0.01 (>0)=True
Loop thru value in columns 1 -5 then updat value in first column that is greater than 0

and column 1 =0
and column 2=5.45
and column 3=0
and column 4-75.00

add
0.01 to column 2
column 2 value= 5.46

just don't know quite how to write it in Access.

 

[PWise]

try column6textboxname.controlsource=iif(QueryCol6>0,iif(QueryCol1>0,QueryCol6+QueryCol1,iif(QueryCol2>0,QueryCol6+QueryCol2,iif(QueryCol3>0,QueryCol6+QueryCol3,iif(QueryCol4>0,QueryCol6+QueryCol4,iif(QueryCol5>0,QueryCol6+QueryCol5,WhatYouWantItToBeIfNoneAreGreaterThenSix))))))

 

[vree]

Have you done this before, the code fails after the first iif(QueryCol6>0,

with the error "Wrong number of arguments", the iif is expecting to evaluate a "true condion" and a "false condition". I don't know if you can nest them like this?


try column6textboxname.controlsource=iif(QueryCol6>0,iif(QueryCol1>0,QueryCol6+QueryCol1,iif(QueryCol2>0,QueryCol6+QueryCol2,iif(QueryCol3>0,QueryCol6+QueryCol3,iif(QueryCol4>0,QueryCol6+QueryCol4,iif(QueryCol5>0,QueryCol6+QueryCol5,WhatYouWantItToBeIfNoneAreGreaterThenSix))))))

 

[PWise]

sorry i did not test that code this peice of code i did test
an dit dose work
sub works
Dim a, Text0NotGreaterThen0, NoneGreaterThen0
Text0NotGreaterThen0 = 2345523
NoneGreaterThen0 = 11111111
a = IIf(Text0 > 0, IIf(Text7 > 0, Val(Text7) + Val(Text0), IIf(Text9 > 0, Val(Text9) + Val(Text0), IIf(Text11 > 0, Val(Text0) + Val(Text11), nonegreatertheno))), Text0NotGreaterThen0)
end sub
i think thai corrected the code for you
column6textboxname.controlsource=iif(QueryCol6>0,QueryCol6+QueryCol1,iif(QueryCol2>0,QueryCol6+QueryCol2,iif(QueryCol3>0,QueryCol6+QueryCol3,iif(QueryCol4>0,QueryCol6+QueryCol4,iif(QueryCol5>0,QueryCol6+QueryCol5,WhatYouWantItToBeIfNoneAreGreaterThen0)))),col6notgreaterthen6)

 

[vree]

Thanks so much for you help! I appreciate it.

 

[vree9638]

Dear PWise,

Thank you for your posts, the examples you gave at least got the brain working.

While i don't know enough to write what i wanted in VB, I did a work around using expressions in a subquery.

I know how to write the If statements, just don't know properly where to do it, using querydef, recordset, recordsetclone etc...
so went this direction:

1. In First Query Determine if there is an amount left due to roundooff:
DIFF: [RegistrationFee]-(Round([RegistrationFee]*[GL4060],2)+Round([RegistrationFee]*[GL5161],2)+Round([RegistrationFee]*[GL5162],2)+Round([RegistrationFee]*[GL5164],2)+Round([RegistrationFee]*[GL5165],2)+Round([RegistrationFee]*[GL5166],2)+Round([RegistrationFee]*[GL5167],2))


In SubQuery:
2. If yes, add it to the first non-zero account for that transaction:

Expr4060: IIf(([DIFF]<>0) And ([rpt4060]<>0),[DIFF]+[rpt4060],[rpt4060])

Expr5161: IIf(([DIFF]<>0) And ([rpt4060]=0) And ([rpt5161]<>0),[DIFF]+[rpt5161],[rpt5161])

and etc for every account that i have.

It's not VB, but it works.

If anyone could help me set this up in a module I would still like to do that.