Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: How to capture a #Error using an If Statement

    I am working with part numbers, and have a query that is using some logic to extract various pieces of the part number string based on the locations of "-" characters (http://goo.gl/NnbvP). That is working fine, but some of the part numbers are not standardized, and therefore the query returns a "#Error" in those cases. I am OK with this, but need to encapsulate that "#Error" and change it to some other string like "Warning". But i am unable to figure out the iif statement that accomplishes this.

    I have tried isnull(), iserror(), and iif([field] = "#Error"... but noe of that is working. Any ideas?
    Last edited by clawlan; 04-11-11 at 13:14.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Don't believe there's any way to do that, as the #Error is not the Value for the control, merely what Access displays when there is a problem evaluating the formula being used.

    What exactly are you doing to extract the data, and what is missing/wrong in the data that trips it up, producing the #Error?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Missinglinq View Post
    Don't believe there's any way to do that, as the #Error is not the Value for the control, merely what Access displays when there is a problem evaluating the formula being used.

    What exactly are you doing to extract the data, and what is missing/wrong in the data that trips it up, producing the #Error?

    Linq ;0)>
    The part numbers should be in the format [A]-[B]-[C] so I am extracting based on the locations of the "-" symbols. Here is a link to that thread (Extracting characters in a string based on multiple "-" placement - dBforums). But in the cases where there is only 1 "-" I get the #Error.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You need to check to see how many dashes are in the string and then run a calculation based on that number. Something like

    =IIf(Len(Me.FieldName) - Len(Replace(Me.FieldName, "-", "")) = 1, Calculation here for 1 dash, Calculation here for more than 1 dashes)
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    I just used some iif statement to isolate the possible error. Not the most elegant, but it works.

    EDIT: I am doing this in the query editor. Have to avoid using VBA.
    Last edited by clawlan; 04-11-11 at 14:03.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by clawlan View Post
    ...I am doing this in the query editor. Have to avoid using VBA.
    That's why I suggested using IIF(). It's the only thing you can do, given where you're performing it!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Missinglinq View Post
    That's why I suggested using IIF(). It's the only thing you can do, given where you're performing it!

    Linq ;0)>
    Yes sir! Thanks for the brain jump start!

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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