Results 1 to 6 of 6
  1. #1
    DB2010MN26 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    37

    MS Access query looking for text in a string that has square brackets

    Hi, I'm trying to build an access query against short text description field and want to pull out any records that have "[ACE]" in the text. Access doesn't seem to like the brackets. How can I modify the query to include these?

    Here is what I currently have and it pulls almost every record

    Criteria: Like "*[ACE]*"

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Try Like "*ACE*"
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you add a field to your query IsACE:IIF(Instr([Description],"[ACE]")>0.True,False) then add True to the criteria row?
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    square brackets have a special meaning when used with Like - see this link https://support.microsoft.com/en-us/...0-242cde582e0b

    suggest try

    Like "*[[]ACE[]]*"

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Like "*[[]ACE[]]*"
    I couldn't get that to work, nor could I concatenate anything preceding like. What are the variants such that Like operator would be required?

    "[[ACE]]", "[(ACE)]", "[(#ACE)#] ??

    Maybe you could use Chr(91) and Chr(93) but not if you need Like operator - or at least not that I've been able to.

    EDIT
    Like "*[[]dog*" seems to work with [dog] and dog records. Only returns [dog]; so does Like "*[[]dog]*"

    Dig deeper at the linked page and you'll see that it says that the ending bracket doesn't require treatment. In fact, it doesn't work if you do thus it's not like it's optional. Whether or not to include it at all might depend on the answer to what the variations are.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    per the link I provided





    [ ] Matches any single character within the brackets. b[ae]ll finds ball and bell but not bill

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Brackets (] or } in Access Report
    By ijaz8883 in forum Reports
    Replies: 13
    Last Post: 10-25-2019, 07:25 AM
  2. Replies: 6
    Last Post: 05-29-2015, 10:21 AM
  3. Replies: 4
    Last Post: 12-05-2014, 11:15 AM
  4. Replies: 1
    Last Post: 07-12-2013, 01:15 PM
  5. Can access take one text string and split it into two?
    By hobsondm01 in forum Database Design
    Replies: 2
    Last Post: 06-09-2011, 09:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums