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,418
    Provided Answers: 7
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •