Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    8

    Unanswered: runtime formula in reports

    Hi all,

    I have been stuck on this problem for sometime and I think I am doing some basic mistake.

    I have a database where I am measuring employees performance quarter by quarter. So I have the two values in my report [Q1 Target] and [Q1 Actual].

    I have a third column in the report where I run the following calculation on runtime [Q1 Actual]-[Q1 Target])/[Q1 Target].

    The problem is that in Q1 Actual and Q1 Target I also have text values. So Q1 Target would be < 1% and the Q1 Actual value would be either acheived or not acheived.

    When this formula runs in runtime on values like the mentioned above, I get #error. I am trying to get rid of this.

    I tried plugging in the following formula but I still get the #error:

    =IIf(IsNull(([Q1 Actual]-[Q1 Target])/[Q1 Target]),NZ(([Q1 Actual]-[Q1 Target])/[Q1 Target]),([Q1 Actual]-[Q1 Target])/[Q1 Target])

    Would you know of how to solve this problem? Or how to approach it differently?

    Thank you.

    Best regards,

    Uzair Moosa

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Computations are supposed to be done on numeric values, so don't mix numeric values with text values. Keep everything numeric and use a formula such as iif([Q1 Target] < ..., "not achieved", "achieved") or something like that to display or print what you want on the report.
    Have a nice day!

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I wonder if you could use something like the isnumeric as an expression to parse out the numbers from the text values? I'm not sure so don't take it as verbatim. You 'might' be able to do something such as iif(isnumeric([Q1 Target]...

    I use it in code but I'm not sure about expressions in a query.

    But as Sinndho stated and you're realizing, it's not a good idea to mix text and numbers in the same field. Consider putting the numeric values in a new field. It might be worth the time to change any coding to do this.
    Last edited by pkstormy; 04-13-10 at 18:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Mar 2010
    Posts
    8

    thank you guys and problem solved

    Thank you guys for your quick responses.

    I agree with you ideally numerics and text should not be mixed.

    In this situation the targets that I am giving are sometimes simply acheived or not achieved and at other times they are an expression such as less than 5% and then other times they are numerics. could not figure out how to have two separte tables and then merge them and then to perform calculations only on the numeric ones.

    but your suggestion was quite good. I have created a new field which is called isnumeric in the table. and then only if isnumeric is 1 I perform the calculation otherwise I leave it as it is.

    Thanks a lot for your help. it helped me generate this thought and come to this solution.

    so thanks a lot and problem solved finally

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    No! Don't name a field isnumeric! - bad idea. This is a reserved word. My suggestion was to use the vba isnumeric command in your expression. If you're going to create a field, I'd call it IsANumber or something like that.

    ex:

    MyExpression: iif(isnumeric([Q1 Actual]),.....)
    Last edited by pkstormy; 04-14-10 at 20:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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