Solved Expression help (1 Viewer)

[Sneale]

Hello,
I need to build a form that includes data from a query but need it to do a calculation when it opens. Fields I need to have calculate are SumOfPointAmt, and NotEligible. NotEligible is a yes/no field. What I need to do is have the form look at NotEligible and if the value is No, return a value of 50, then multiply the SumOfPointAmt by 2 and add the two numbers together to get a total. if the NotEligible field is True, then return just the SumOfPointAmt multiplied by 2. In Excel it looks like this:

=IF(NotEligible="No",(50+(SumOfPointAmt*2)),SumOfPointAmt*2)

I am not even sure if this is possible but would appreciate any input you may have!

 

[theDBguy]

Hi. Maybe you could try this.

=IIf([NotEligible]="No",50,0)+([SumOfPointAmt]*2)

Hope that helps...

 

[Sneale]

That worked! thank you very much!

 

[theDBguy]

That worked! thank you very much!

Hi. You're welcome. Glad we could assist. Good luck with your project.

 

[Sneale]

one thing seems to be an issue with this. if there is no value in SumOfPointAmt, it doesn't seem to return anything in the report field.

 

[theDBguy]

one thing seems to be an issue with this. if there is no value in SumOfPointAmt, it doesn't seem to return anything in the report field.

Try this:

=IIf([NotEligible]="No",50,0)+(Nz([SumOfPointAmt])*2)

 

[Sneale]

Try this:

=IIf([NotEligible]="No",50,0)+(Nz([SumOfPointAmt])*2)

This worked great! Thank you so much!

 

[theDBguy]

This worked great! Thank you so much!

Excellent! Glad to hear that worked for you. Cheers!