Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Texas
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    searching for # using instr (XP)

    I've been able to use the following as search criteria in a query: like "*apt lb_ sign*". Now I need to be able to pull the string that begins with "apt lb_sign" from the end of the records that the query found. Using instr(1,field name,"apt lb_sign") throws an error. Is there some way I can tell Access to use the lb_sign(#) as a regular search character not a wildcard character?

    I had to replace the pound-sign(#) with lb_sign in all the above because it wouldn't print!

    Thanks in advance,

    Mary

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: searching for # using instr (XP)

    Not very clear for me what you're asking.
    Did you want something like :
    Right([field name],11) = "apt lb_sign"
    Francois

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

    Re: searching for # using instr (XP)

    To use Like operator with a wildcard symbol such as asterisk (*) or pound sign (#) use pair of open & closed brackets to delimit the symbol. To extract the portion of the field that begins with the pound sign you can use Mid function. Sample SQL:

    <code>SELECT Table1.Field1, Mid$(<!t>[Field1]<!/t>,InStr(<!t>[Field1]<!/t>,"#")) AS Expr1</code>
    <code>FROM Table1</code>
    <code>WHERE (((Table1.Field1) Like "*<!t>[#]<!/t>*"));</code>

    This syntax worked OK in Access 2K. I was also able to use InStr function to select records with pound sign w/o any errors. Example:

    <code>SELECT Table1.Field1, InStr(1,<!t>[FIELD1]<!/t>,"#") AS Expr1</code>
    <code>FROM Table1</code>
    <code>WHERE (((InStr(1,<!t>[FIELD1]<!/t>,"#"))>0));</code>

    HTH

  4. #4
    Lounger
    Join Date
    Mar 2001
    Location
    Texas
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: searching for # using instr (XP)

    Using the [#] worked in the query string portion but it does not work in the instr function. Still get an error when I try to instr(1,string,"apt [#]"). Any other suggestions?

    Thanks,

    Mary

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

    Re: searching for # using instr (XP)

    I had no problems using InStr function with pound sign (with or w/o other characters) to select records. You would not use the brackets with InStr, just delimit the search text (verbatim) with quotes. Though it shouldn't make any difference, you could try omitting the optional first argument, start position (required only if also using the optional fourth argument, comparison type). Example:

    <code>SELECT Table1.Field1, InStr(<!t>[FIELD1]<!/t>,"APT#") AS Expr1</code>
    <code>FROM Table1</code>
    <code>WHERE (((InStr(<!t>[FIELD1]<!/t>,"APT#"))>0));</code>

    Otherwise you will have to post the SQL you are using and the context in which it is being used to try to determine cause of error. Also, what is the specific error you are getting?

    HTH

Posting Permissions

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