Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2016
    Posts
    1

    Unanswered: #error in expression builder Iif statement

    I am using the expression builder with the following string. Any instances that = "" are correctly showing as "", but a couple still get a #Error in the field. Any suggestions on how to expand this expression with error handling so these do not occur?

    Code:
    Vendor Cat #: (IIf([dbo_case_record_supply]![crs_comment]="","",Replace(Mid([dbo_case_record_supply]![crs_comment],2,InStr(1,[dbo_case_record_supply]![crs_comment],";")-2),"#","",1,1)))

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,468
    Provided Answers: 10
    I would say the field Has Nothing in it

    its NULL

    If I was doing it i would Turn it in to a VB function

    in a module create a function

    function VenderCat(crs_comment)
    '
    'the Logic
    '
    '
    VenderCat = THE_ANSWER
    End Function

    them in the Query

    Vendor_Cat #: VenderCat([dbo_case_record_supply]![crs_comment])

    Spaces in field name not a good idea.
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    Join Date
    Feb 2014
    Posts
    11
    Hi,

    Quote Originally Posted by jsamuel1993 View Post
    ... but a couple still get a #Error in the field.....
    A possible an easy way to fix the problem is to hide the original field that could receive the #error value with the same formula :
    Code:
    (IIf([dbo_case_record_supply]![crs_comment]="","",Replace(Mid([dbo_case_record_supply]![crs_comment],2,InStr(1,[dbo_case_record_supply]![crs_comment],";")-2),"#","",1,1)))
    You name it Vendor_Cat_tmp

    Then, in design mode, ou add another textbox field named Vendor_Cat where you type the new formula :
    Code:
    IIf(IsError([Vendor_Cat_tmp]), "Sorry, no match !", [Vendor_Cat_tmp])
    Argy

Posting Permissions

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