Results 1 to 15 of 15
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Filter for # in a text string (Access XP)

    Hi,
    In a list of product codes, some have a # symbol. (Etc, QTV#123, YP#189)
    As # is a wildcard for a number, how can I filter for all text based product codes that have a # sign in it? I recall using ~ infront of a wildcard to filter for that character...but it does not work here!
    Tx
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter for # in a text string (Access XP)

    Rudi if they are to be in this format

    XXX#111 then use Like"*#*"

    will work, if the octothorp has a letter after it it won't!
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter for # in a text string (Access XP)

    You can use a string for the filter like Instr(1, [field], "#") > 0. as the filter.
    Richard

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter for # in a text string (Access XP)

    Richard

    Much more efficient <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

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

    Re: Filter for # in a text string (Access XP)

    Your problem has already been solved, but as an addition: # is not a wildcard character, and ~ is used in Excel to specify a wildcard character * or ? as a literal character in a search.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter for # in a text string (Access XP)

    Hi Jerry
    Here is a sample DB. I already tried this, and it did not work. This was the first thing I did until I discovered the results were not accurate.
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter for # in a text string (Access XP)

    Jerry,
    Here is the attachment....sorry I forgot to add it in your reply!

    Hans,
    In the attachment is a piture of access's help files showing the # to be a wildcard? So whats the bottom line?

    Also, i already tried Jerry's suggestion and Richards suggestion is also not working. (May due to my error???? But I copied and pasted his suggested filter, modified it to =InStr(1,[Product Name],"#")>0 and I get no records?

    The attachment has a dummy DB to prove the scenario!
    Regards,
    Rudi

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

    Re: Filter for # in a text string (Access XP)

    Try this then:
    <code>
    Like "*[#]*"
    </code>
    BTW,
    > Here is the attachment....sorry I forgot to add it in your reply!
    How had you planned to attach a file to someone else's reply? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter for # in a text string (Access XP)

    Thankyou Hans...that works well!

    About the attachment....Lets call it - Like " * [early morning blues] * "... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Regards,
    Rudi

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter for # in a text string (Access XP)

    In addition. hans's example will work if it is in the format AAAA#NNN (A is alpha, N is number) and works fine for you formatted product numbers in this example. For some reason it does not like alpha's after the #

    If in the future you are going to this format (say) AAAAA#ANN, this criteria will work with all:

    InStr(1,[product name],"#")>"0"

    Another tool for your toolbox <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

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

    Re: Filter for # in a text string (Access XP)

    If I change some of the product names in the Product Name field in the table in Rudi's sample database to contain letters or other non-numeric characters after the #, the query still works fine using
    <pre>Like "*[#]*"
    </pre>

    as criteria. See screenshot.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter for # in a text string (Access XP)

    Thanks Hans I take it all back, I was testing it on Rudi's db and I did not see my examples on the AAAA#ANN changes, it rather confused me for a moment, as I know your example should have worked, shall I say I am myopically challenged this morning. Need <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>.
    Jerry

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter for # in a text string (Access XP)

    I have records where a field could contain *** or **** or ***** or no Asterisks or a text value or null

    Trying to query records that has a field that contains **** (4 Asterisks)

    With Criteria: Like "*[****]*"

    It is returning all records with *** and **** and ***** Asterisks

    What do I change to query just the records with 4 ****?

    Thanks, John

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

    Re: Filter for # in a text string (Access XP)

    Set the criteria to
    <code>
    ="****"
    </code>
    The = prevents Access from adding the word Like.

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter for # in a text string (Access XP)

    Thank you for helping me with "About using wildcard characters to search for partial or matching values"

    John

Posting Permissions

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