Custom Control in Report help

[sullyscsu]

I am designing this report for work to determine whether or not the $ change for a particular project requires review by a higher authority or not. It is setup as follows:

Project# | Proposed Change | Currently in Budget | Var. | Requires Review?

all but the last column are pulled from the record source, which is a query I setup and the required review is a custom control that has logic built into it. The logic is as follows (using expression builder):

=IIf([Variance]>=250000,"Yes",IIf(Eval([WB2010]<=100000 And Abs([Variance])/[WB2010]>=0.25),"Yes",IIf(Eval([WB2010] between 100000 And 250000 And Abs([Variance])>=25000),"Yes",IIf(Eval([WB2010] between 250000 And 2500000 And Abs([Variance])/[WB2010]>=0.1),"Yes",IIf(Eval([WB2010] between 2500000 And 5000000 And Abs([Variance])>=250000),"Yes",IIF(EVAL([WB2010]>5000000 AND ABS([Variance])/[WB2010]>=0.1),”Yes”,”No”))))))

after I entered in this logic I got an error saying I was missing a ) or ] or |.

I looked at this a million times... not sure what is wrong. Can someone take a look at this? I think I just need another pair of eyes. Also, when I run the report, the very last "Yes" and "No" is displayed: ["Yes"], ["No"] and access asks for a parameter value for each.

Thanks in advance!

 

[Galaxiom]

You would be better off reconstructing this expression as a function.
Use a Case statement in the function rather than a bunch of If statements.

I would also include the variance comparison values as arguments in the function.

 

[sullyscsu]

would you mind giving me a snippett of what that function might look like?

Thanks.

 

[Rich]

Reorted !

 

[sullyscsu]

reorted !

huh???????????

 

[Rich]

huh???????????

Slight typo it should have read Reported, there was a spammer post in amongst other threads here, but it's now been removed, so no worries

 

[sullyscsu]

I solved this problem.. I ended up using a bunch of if's and else if's in VBA in the report's format event

 

[vbaInet]

Glad you got it figured out. That was exactly what GalaxiomAtHome was talking about. For reuseability, you could put that code in a function and call the function when needs be.