Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139

    Unanswered: Expression formula

    Hello;

    Im 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 Im 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. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  3. #3
    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. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    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

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    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? Im interested because as you suggest, ratings and metrics do change, and sometimes frequently.

    Thanks...

    Larry

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote 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.
    Paul

  10. #10
    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. #11
    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
  •