Display a certain word depending on the value of a specific text box

[isaacski]

Hi All!,

I have a database that's very basic. There is one table that has every field that I need to collect. I have a user form to collect this data. I also have a report that shows the scores recorded. I have a text box that shows the higher of two values in two different fields. (Quiz 1 = 90, Quiz 2 = 80, will display 90). I want another nearby unbound text box to show the word "Exceptional" if this score above a 95, "Successful" if a score is between 85-95, "Partially Successful" if the score is between 80-85, and "Unsuccessful" if the score is 80 or below.

I have gotten this to work with just 95 or above (but it reports the same for all records in the report so its not performing the function on individual records). I haven't figured out how to include the ranges... I have fooled around with some ElseIF formulas but haven't been successful...

Anyone have some advise?

Thanks in advance,

Kim

 

[pr2-eugin]

Are you using If-Then-Else or IIF() ??

 

[isaacski]

I believe I am using If - then - else when in VBA; I tried using iif in the expression builder and there i got it to work correctly with the above 95, I just couldn't get any of the ranges in there... for vba it's been looking similar to:

If ([txtwarrantyavg] >= 0.95) Then
Me.Text12 = "Exceptional"
ElseIf...

Tell me if this is incorrect but I would much rather use a simple expression in the control source if possible as then I don't have to make it a vba event (i.e. the value will display automatically and I won't have to "click" in the box to run it)

Obviously I'm pretty new at this soo... ya.

 

[plog]

I'd either make a table to hold this data or create a custom function. The table would look like this:

LowRange, HighRange, Message
0, 79, "Unsuccessful"
80, 84, "Partially Successful"

Then do a DLookup using the current score in the criteria argument.

 

[pr2-eugin]

Try this in the text boxes control source..This is a IIf(), look up the link for more info

IIF([COLOR=Blue]maxBox[/COLOR] >= 95, "Exceptional", IIF([COLOR=Blue]maxBox[/COLOR] Between 85 And 94, "Successful", IIF([COLOR=Blue]maxBox[/COLOR] Between 80 And 84, "Partially Successful", "Unsuccessful")))
[COLOR=Green]' Please change all blue bits to match the name of the Box you have[/COLOR]

EDIT: Or try using plog's method.. If you are not confident in using multiple If criteria..

 

[isaacski]

I was hoping to not have to add unncessary tables but that is definately a viable suggestion! My only issue is that I have to do this for multiple boxes and each definition is different...

I will try it though as it seems to be the most simple way of doing it!

Thanks for the help!

 

[isaacski]

pr2... I did try that but I'm thinking the syntax might have been wrong.. I will try that option!!!

currently all of my previous fields have changed to #Name for some reason so I guess I have to figure that out first.. I'll let you know how it goes!

 

[isaacski]

Your code worked PR2... thanks very much!!!!

 

[isaacski]

Alright guys,

I have used this formula on many of the text boxes in my report, tweaking the values and text boxes of course. There is one box, the weekly quiz text box that I can NOT get to display properly. The box is an average and i've checked to make sure it is calculating correctly, it is. The formula I've checked, re-written from scratch, over and over and it is correct. I've double checked the format of this field in the table, form and report, all should be fine. However, for some reason it displays "Unsuccessful" for values that shouldn't be displayed as unsuccessful...

The db is 3. something mb so I can't upload it apparently... I've spent over 2 hours trying to figure out why the formula worked on every other box except this one...

Help?
:banghead:

Kim

 

[pr2-eugin]

The db is 3. something mb so I can't upload it apparently... I've spent over 2 hours trying to figure out why the formula worked on every other box except this one...

Try stripping down the database, delete all Queries/Tables/Reports that are not in question.. Or if you have only a few Tables/Queries delete some data leave just 10-15 records that will give an output.. Do a Compact and Repair, this will bring the size down..

 

[isaacski]

Thanks again! 3 for 3?