Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    InStr() (2000/SP-3)

    I am getting an error with the following code:

    <font color=blue>If InStr(tbl.Name, "ImportError", vbTextCompare) > 0 Then</font color=blue>

    When checking with Debug, it proclaims that there is a type mismatch on the return value of InStr(). What am I doing wrong?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: InStr() (2000/SP-3)

    If you want to use the optional Compare argument (4th arg), you also must provide the (optional) 1st argument, Start. You also can provide the first arg and omit the last, or omit both optional arguments. Examples:

    ? InStr(1,"ImportErrors", "ImportError", vbTextCompare)
    1

    ? InStr(1,"ImportErrors", "ImportError")
    1

    ? InStr("ImportErrors", "ImportError")
    1

    ? InStr("ImportErrors", "ImportError", vbTextCompare)
    ' Err 13, Type Mismatch

    You get a Type Mismatch error because Tbl.Name (which I assume refers to Name property of a TableDef or similar object) is a string, while the Start argument requires a numeric expression. As noted in VBA Help for InStr function:

    start Optional. Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. If start contains Null, an error occurs. The start argument is required if compare is specified.

    And:

    compare Optional. Specifies the type of string comparison. If compare is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison. Specify a valid LCID (LocaleID) to use locale-specific rules in the comparison.

    To be safe, I always specify both optional arguments when using InStr so there's no ambiguity as to whether the comparison is case-sensitive or not. Simply provide 1 for the first argument if the entire string is being evaluated.

    HTH

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: InStr() (2000/SP-3)

    Added: Forget this. See MarkD's reply below.

    Check Tools | References... If there are references starting with MISSING, note which they are, then clear the corresponding check boxes.

  4. #4
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: InStr() (2000/SP-3)

    I missed that little statement (The start argument is required if compare is specified.) Damn my eyes.

    That did it. Thanks.

Posting Permissions

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