1. Registered User
Join Date
Aug 2005
Location
Delaware
Posts
139

Hello;

I’m trying to come up with a formula for an unbound control on a report. I have likely butchered it because the system returns an error of “The expression you entered contains invalid syntax”. The expression entered is:
= if ( [Text20] "","",if [Text20] <90,"1",if [Text20] <94.99,"2", if [Text20] <100,"3", if [Text20] <125,"4",5)

What I’m looking to do is return a value based on the contents of another unbound report control. If the value of [Text20] is less than 90, [Text37]=1, if the value of [Text20] is between 90 and 94.99, [Text37] = 2, if the value of [Text20] is between 95 and 100, [Text37] = 3, if the value of [Text20] is between 100.01 and 124.99, [Text37] = 4, if the value of [Text20] is greater than 125, [Text37] = 5.

To add even more complexity to the expression, the value of [Text37] can not be greater than 4 if the value of [Text22] is less than 100.

Can someone tell me how to construct the expression? Or, should this calculation be done in the query that pulls the data for the report?

Thanks...

Larry

2. Registered User
Join Date
May 2005
Location
Posts
2,888
Your 2 main problems are that the function is IIf(), not If(), and each must stand alone. In other words, the formulas inside the main formula still need the proper parentheses and such. Try again with those 2 in mind and see where you are.

3. Registered User
Join Date
Aug 2005
Location
Delaware
Posts
139
Thanks pbaldy;

This works:
=IIf([Text20]<90,"1",IIf([Text20] Between 90 And 94.99,"2",IIf([Text20] Between 95 And 100,"3",IIf([Text20] Between 100.01 And 124.99,"4","5"))))

However when I attempt to use:
=IIf([Text20]<90,"1",IIf([Text20] Between 90 And 94.99,"2",IIf([Text20] Between 95 And 100,"3",IIf([Text20] Between 100.01 and 124.99 and [Text22]<100,"4","5")))) it returns a 5, even though [Text20]=120 and [Text22]=100

What am I doing wrong with the examination of [Text22]?

Larry

4. Registered User
Join Date
May 2005
Location
Posts
2,888
Well, your final test includes "[Text22]<100". Your sample data is "[Text22]=100" which clearly fails that test, so 5 would be the correct result. IOW, less than is different than equal than.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
wouldn't it be easier to do the processing in a function called from the reports detail event (forget which, probably detail_format)

eg
myunboundcontrol = GetRating()

you'd have the added advantage that its easier to understand, easier to extend or modify

6. Registered User
Join Date
Aug 2005
Location
Delaware
Posts
139
Thank you pbaldy and healdem;

pbaldy... the test should fail because [Text20] is less then 124.99, even though [Text22] = 100.

healdem... you lost me. I might not be understand the concept correctly but the (unbound) control I'm trying to program is in the detail section. This is a monthly report that displays an employee's performance measurements and ratings for 5 metrics. It displays the current month, previous month and the YTD for each of the 5 metrics. Then I have 5 controls which "rate" the current performance, and 5 controls which "rate" the YTD performance. These rating are on a scale of 1 to 5, 5 being the best rating. However as you can see, the top performance rating can only be achieved if your quality is 100%.

Thanks...

Larry

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
There is a great deal of processing that you can do in code in a report (or for that matter a form). You dont need to do every thing in a query
you dont need to do everything in a controls source.
you can set controls on a report which dont print

For the problem you outline I would suggest that a fucntion makes more sense than a complex iif, expecially if the same basic iif statement is used in more than one control

This approach will especilly pay dividends if there is a risk that a new grading scale will be intorduced, or if a new rating item is introduced

The way access works is that it triggers a series of events (or hooks), which if there is code associated with the event will fire. The events that are of usual interest are nodata, group header & footer, and detail format & print. placing code in these events gives you thre ability to extend report functionality and get round some of those seemingly impossible roadblocks. In my view Access does a pretty good job in most of the everyday reporting problems however it can run out of steam on complex reports.

Personally if I find im using a complex statement as the controls recordsource (often bigger than the space in the properties box) then thats a good time to consider placing some of the complexity in the report.

HTH

8. Registered User
Join Date
Aug 2005
Location
Delaware
Posts
139
Hello Healdem;

Are you talking about putting the formula in the OPEN event of the form or something like that? Until I understand it better I think you are suggesting a procedure that is beyond my meager capabilities. Can you provide an illustration or elaborate further? I’m interested because as you suggest, ratings and metrics do change, and sometimes frequently.

Thanks...

Larry

9. Registered User
Join Date
May 2005
Location
Posts
2,888
Originally Posted by Larryg
pbaldy... the test should fail because [Text20] is less then 124.99, even though [Text22] = 100.
Then why are you questioning the result of 5, which is the correct return value when a test value fails the test?

And for the record, I agree with moving this off to a function and calling it as needed. It's certainly what I would do in this situation.

10. Registered User
Join Date
Aug 2005
Location
Delaware
Posts
139
Thanks pbaldy;

For the benefit of others... here is what worked:

=IIf([Text20]<90,"1",IIf([Text20] Between 90 And 94.99,"2",IIf([Text20] Between 95 And 100,"3",IIf([Text20] Between 100.01 And 124.99,"4",IIf([Text20]>=125 And [Text64]<100,"4","5")))))

[Text20] = 152
[Text64] = 99.5

Above formula returned a 4, which is what it should have returned. Thanks to all for your help.

Larry

11. Registered User
Join Date
Jul 2006
Posts
108
i dont know much about expressions and i really dont know the IIF command bu here it is in VBA:

dim check as integer
dim return as integer
dim check22 as integer
check22 = text22.text
check = text20.text
return = 0

if check <= 90 then
return = 1
elseif check > 90 and check <= 95 then
return = 2
elseif check > 95 and check <= 100 then
return = 3
elseif check > 100 and check < 125 and check22 < 100
return = 4
else
return = 5
endif

*note that the above will return "5" if text20 = 125

it will also return "5" if text20 is 125 and check22 is less than 100

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•