Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2007
    Posts
    16

    Unhappy Unanswered: Iff function Error when using Left

    Hi

    Can some one please tell me why the following does not work.

    IIf(Left([Product],InStr(1,[Product],"mSpec") Like "*[! 1-34]*"),(Left([Product],InStr(1,[Product],"mSpec",1212))))

    What I am trying to do is take out the number before "mSpec" but not all of the record have a number before it so for them records it displays an error. So I want to use IIf function so if there is a number it displays the number if there is no number it needs to display "1212".

    Thank you

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use the Nz function to substitute something to Null records:

    IIf(Left(Nz([Product],...

    Have a nice day!

  3. #3
    Join Date
    Jul 2007
    Posts
    16
    Hi
    Thanx for your reply

    What you recommeded works but not for all of the records;-
    IIf(Nz(Left([Product],InStr([Product]," mSpec") Not Like "[!1-34]")),0,Left([Product],InStr([Product]," mSpec")))

    Is this correct. What you have recommended works but for the records which the outcome is false it displays an error. Instead of displaying information from the left side of the product column.

    Thank you

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As I told you yesterday on the other thread on this subject (don't remember if it was here or on another forum) Memo Fields are not designed to hold data that will need to be manipulated in any fashion. Trying to do so will always ends up in erratic results. Data that needs to be manipulated in any way, shape or form needs to be stored in discrete fields, not bunched together in a Memo Field. Memo Fields are designed to Memos, i.s. notes.They are not to be used to group by, sort by, or anything else except view or printed out.
    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
    Jul 2007
    Posts
    16
    Hi

    The data is imported so it is save as a memo. what feild type should such data be saved as.

    Thank you

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Unless it's over 255 characters, as Datatype Text. If it's larger than that something really needs to be done before importing.
    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
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be something like that:
    Code:
      Value = IIf(Left(Nz(!Product, ""), InStr(Nz(!Product, ""), " mSpec")) Like "*[! 1-34]*", Left(Nz(!Product, ""), InStr(1, Nz(!Product, ""), " mSpec")), "1212")
    Have a nice day!

Posting Permissions

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