Populating a "Text" field in a Report

[two_smooth]

Hello Access Forum Members,

My issue is, I have two "text" boxes on a report (text box 1 and text box 2). Text box 1 has the expression "=[data1]+[data2]+[data3]+[data4]". The results are stored/printed in text box 1 of the report.

I need to take the results in "text box 1" using less than and greater than logic (expressions) and set the results of text box 2 based on a series of "ranges". The logic for the ranges are as follows:

If text box 1 results are greater than 500, text box 2 equals a value of "GOLD";
If text box 1 results are between 400 and 499, text box 2 equals a value of "SILVER";


If text box 1 results are between 300 and 399, text box 2 equals a value of "BRONZE";

HELP PLEASE, ASAP

[ConneXionLost]

Hi,

Set this as the control source of text box 2:

=IIf(([data1]+[data2]+[data3]+[data4])>=500,"Gold",IIf(([data1]+[data2]+[data3]+[data4])>=400,"Silver",IIf(([data1]+[data2]+[data3]+[data4])>=300,"Bronze","")))

Cheers,

[RuralGuy]

Did the solution supplied by ConneXionLost work for you?

[two_smooth]

No, it did not work. Text box two displyed as "blank".

[RuralGuy]

Try this as the ControlSource of [Text Box 2] as a test:
=IIf([Text Box 1]<>0,"Gold","Bronze")
...using the actual names of the controls of course.

[two_smooth]

RG,

I tried your test suggestion and with one record having a value of 500 in text box 1 it displayed "GOLD", all other records displayed, "BRONZE".

two_smooth

[RuralGuy]

So all other records are 0 or are they Null (no value)?

[two_smooth]

RG,

Yes, all the other records are "0 or null".

two_smooth

[RuralGuy]

So TextBox1 is bound to a Text field and not all of the fields have been initialized, right? Numeric fields do not allow Nulls and will initialize to 0.

[two_smooth]

RG,

Text box 1 is a computed field from data fields in the record (=[data1]+[data2]+....). Data1 and all other data# fields are initialized to "0".

two_smooth

[RuralGuy]

OK, try ConneXionLost's suggestion but make a slight change:
=IIf([TextBox1]>=500,"Gold",IIf([TextBox1]>=400,"Silver",IIf([TextBox1]>=300,"Bronze","")))

[two_smooth]

RG,

I've tried ConneXionLost's with your suggested slight change as follows:

=IIf([Text1]>=500,"GOLD",IIf([Text1]>=400,"SILVER",IIf([Text1]>=300,"BRONZE",IIf([Text1]>=200,"HONORABLE MENTION"""))))

I populated four records as follows:
Text1=500
Text1=400
Text1=300
Text1=200

The report only displayed Text2="GOLD", the other three records for Text1 and Text2 displayed "blanks/null" values.

two_smooth

[RuralGuy]

I never liked those nested IIF's anyway. How about creating a function in your Report Class module that returns the proper results? I would probably use a Select Case structure.

[RuralGuy]

Your function is going to look something like:

Function Award(InValue As Variant) As String
    Select Case Nz(InValue, 0)
        Case Is >= 500
            Award = "GOLD"
        Case Is >= 400
            Award = "SILVER"
        Case Is >= 300
            Award = "BRONZE"
        Case Else
            Award = ""
    End Select
End Function

...and the ControlSource of [TextBox 2] will be:
=Award([Text Box 1])

[two_smooth]

RG,

Exactly where do I "code" the function?

two_smooth