IIF-then statement to calculate data from one of two fields

[woodybware]

I currently am using the following coding to calculate a score based upon the GPA that is found in the form field txtBSNCummulativeGPA.

=IIf([txtBSNCummulativeGPA]>=4,3,IIf([txtBSNCummulativeGPA]>=3.9,2.8,IIf([txtBSNCummulativeGPA]>=3.8,2.6,IIf([txtBSNCummulativeGPA]>=3.7,2.4,IIf([txtBSNCummulativeGPA]>=3.6,2.2,IIf([txtBSNCummulativeGPA]>=3.5,2,IIf([txtBSNCummulativeGPA]>=3.4,1.8,IIf([txtBSNCummulativeGPA]>=3.3,1.6,IIf([txtBSNCummulativeGPA]>=3.2,1.4,IIf([txtBSNCummulativeGPA]>=3.1,1.2,IIf([txtBSNCummulativeGPA]>=3,1.0)))))))))))

In some cases, the txtBSNCummulativeGPA field might be blank, and in such cases I would like the function to then apply the value in the txtOtherBSNGPA field on this form to the calculation above. So essentially if txtBSNCummulativeGPA is null, then factor txtOtherBSNGPA. If the txtBSNCummulativeGPA has a value, that should be the default.



Thanx!

[June7]

Try Nz function: Nz([txtBSNCummulativeGPA], [txtOtherBSNGPA])

However, this expression is getting rather long and might exceed limit. Long names chew up that limit.

Choose and Switch functions can often serve instead of nested IIf but will still be a long expression.

Consider a textbox named txtGPA on form/report with expression: =Nz([txtBSNCummulativeGPA], [txtOtherBSNGPA])

Then use that textbox in the conditional expression:

=Switch([txtGPA]>=4,3, [txtGPA]>=3.9,2.8, [txtGPA]>=3.8,2.6, [txtGPA]>=3.7,2.4, [txtGPA]>=3.6,2.2, [txtGPA]>=3.5,2, [txtGPA]>=3.4,1.8, [txtGPA]>=3.3,1.6, [txtGPA]>=3.2,1.4, [txtGPA]>=3.1,1.2, [txtGPA]>=3,1)

[woodybware]

I like the option you proposed and can just use the txtGPA as a hidden field. In my case though, txtOtherBSNGPA will often be null. How do I pull up the form without it requesting data for txtOtherBSNGPA or txtBSNCummulativeGPA using the example you provided when one field is null?

[June7]

Don't understand, why would there be a request for data? Are txtOtherBSNGPA and txtBSNCummulativeGPA the names of fields or textboxes? Will one or other always have a value?

[woodybware]

Sorry, let me clarify, the document I am working on is actually a report that is pulling the data for txtOtherBSNGPA and txtBSNCummulativeGPA from a Form called MSNEvaluation. When I generate the report using the coding you provided, the report requests a value for txtOtherBSNGPA when it is null.

The control source for txtBSNCummulativeGPA is =Forms!frmMSNEvaluation!txtBSNCumGPA
The control source for txtOtherBSNGPA is =[Forms]![frmMSNEvaluation]![txtOther_Bachelors_Cum_GPA]

Might it be easier to create a non-visible field txtGPA like you suggested and have the control source reference back to the forms? So txtGPA would equal txtBSNCumGPA, but if txtBSNCumGPA was null, would equal txtOther_Bachelors_Cum_GPA. What would the control source code look like for txtGPA in this scenario?

[June7]

I don't understand why report would request a value if [Forms]![frmMSNEvaluation]![txtOther_Bachelors_Cum_GPA] is null. Whether that form control is Null, a number, text, or empty string, the report control should just reflect that. The expression in txtGPA references report controls, not fields, so setting the txtGPA textbox to an expression that references the two form controls will probably not resolve this.

Why are these GPA values pulled from a form and are not data in the report RecordSource?

If you want to provide db for analysis, follow instructions at bottom of my post.