textbox values depending on value in another textbox

[epardo87]

Hello all,

I have in a single form two text boxes, the first one pulls the value from a query, plus it is formatted in percentage, this is the code:

=IIf(IsNull([Forms]![frm20MainStatus]![cmbStation]),"",FormatPercent(
IIf(IsNull(DLookUp("[ValidCert]","[Qry-CertStatStatCount]","[StationDescription]='" & 
[Forms]![frm20MainStatus]![cmbStation] & "'")),0,
DLookUp("[ValidCert]/[GoalTotal]","[Qry-CertStatStatCount]","[StationDescription]='" & 
[Forms]![frm20MainStatus]![cmbStation] & "'")),0))

So the resulting value of the fisrt textbox is a percentage, now what I need is that the textbox2 show a text depending on the range of percentage in textbox1 so that:
if textbox1 = 100% then "OK"


if between 99% and 40% then "Pending"
if between 0% and 39% then "Attention"

I already tried a few options but couldn't make it work,

I'd appreciate any suggestion, regards!

[Micron]

I think I would make the textbox control source a simple function that returns the text you need, almost as you've written the requirement. That might be an issue if you have the textbox bound to a query or table.

[NTC]

Your second text box can either be bound or unbound; if bound then the code is used to write in a value at a triggered event, if unbound then it is the value of the textbox itself:

unbound Text2 field: =iif([textbox1] = 100%,"OK",iif([textbox1] <= 99% AND >= 40%,"Pending", "Attention"))

But the presentation of a number, involving %, and the actual number stored can vary - and so you may vary that IIF statement above to remove the % and potentially instead use decimals i.e. 1, .99, etc.

The reason this may not initially work is probably going to be due to whether Textbox 1 is actually a number field - and I tend to think it is not as IIF statements typically result in text field types - and you cannot do a math compare on a text field. For sanity testing purposes put a temporary field on your form where you are certain it is holding a numerical field in order to test the nested IIF statement I provided.

[epardo87]

Your second text box can either be bound or unbound; if bound then the code is used to write in a value at a triggered event, if unbound then it is the value of the textbox itself:

unbound Text2 field: =iif([textbox1] = 100%,"OK",iif([textbox1] <= 99% AND >= 40%,"Pending", "Attention"))

But the presentation of a number, involving %, and the actual number stored can vary - and so you may vary that IIF statement above to remove the % and potentially instead use decimals i.e. 1, .99, etc.

The reason this may not initially work is probably going to be due to whether Textbox 1 is actually a number field - and I tend to think it is not as IIF statements typically result in text field types - and you cannot do a math compare on a text field. For sanity testing purposes put a temporary field on your form where you are certain it is holding a numerical field in order to test the nested IIF statement I provided.

Thanks!

I tried this code but it gives me invalid syntax, I tried all combinations with the numbers without success (decimals, percentage, decimals with "), i think that the expression builder is not recognizing the "AND" command here...

=IIf(IsNull([Forms]![frm20MainStatus]![cmbStation]),"",IIf([Forms]![frm20MainStatus]![txtPerStat]="1","OK",IIf([Forms]![frm20MainStatus]![txtPerStat]<="0.99" AND >=".4","Pending","Attention")))

I did this one and it works partially, it is extrange because, it works fine when 100%, 50% and 0% but if the result is 33% it gives me "Pending" even though the rule is to be "Attention" when lower than 40%, what could be happening?

=IIf(IsNull([Forms]![frm20MainStatus]![cmbStation]),"",IIf([Forms]![frm20MainStatus]![txtPerStat]="1","OK",IIf([Forms]![frm20MainStatus]![txtPerStat]<"0.4","Attention","Pending")))

[Micron]

You're reinforcing my thinking that complex nested IIf statements are a thing (for me at least) to avoid whenever possible.

[andy49]

Can you have <"string". (I didn't think so)

What about using iif (val(form stuff) <0.4, etc




Sent from my iPhone using Tapatalk

[NTC]

You are all tangled up in text format versus number format. Anything wrapped in quotes is a text string and compares are dicey. Somewhere must be a table with true numbers. Work a record source (table/query) with numbers at lease for the purpose of making your IIF statement work correctly. Keep in mind that the IIF could occur as a calculated field in a query rather than at the form level - and this approach might better. Once you are clear your IIF works - then decide on how to best present the info.

[epardo87]

well,

I removed the "Percentformat" function from the first textbox and now it works great, the thing is that I need the first textbox to be shown in percentage instead of decimals, I tried changing it from the properties panel but it doesn't even have the option. Another thing is that the dlookup in the first textbox is dividing the data in two columns, I also tried making the operation directly in the query, converting it to percentage and retreiving that column to the textbox but it gives error and crashes access =S.

how else can I convert a looked up result in a textbox from decimals to percentage without using the Percentformat function?

[epardo87]

Ok finally I fixed it, the trick was just to change in the second textbox the criteria, instead of having it <"0.4" I just changed it to <"40%" and that allowed me to keep using the Formatpercent function in the first textcode, so this are the final codes:
Thank you all for your answers
first textbox:

=IIf(IsNull([Forms]![frm20MainStatus]![cmbStation]),"",
IIf(IsNull(DLookUp("[ValidCert]","[Qry-CertStatStatCount]",
"[Estación]='" & [Forms]![frm20MainStatus]![cmbStation] & "'")),"0%",
FormatPercent(Round(DLookUp("[ValidCert]/[GoalTotal]","[Qry-CertStatStatCount]","[Estación]='" 
& [Forms]![frm20MainStatus]![cmbStation] & "'"),2),0)))

Second textbox:

=IIf(IsNull([Forms]![frm20MainStatus]![cmbStation]),"",
IIf([Forms]![frm20MainStatus]![txtPerStat]="100%","OK",
IIf([Forms]![frm20MainStatus]![txtPerStat]<"40%","Attention","Pending")))