Results 1 to 12 of 12
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    Finding a wild card in a text string

    MS Access 2007

    What is the syntax for finding a wild card in a text string.
    Specifically, I'm trying to find a # sign when a user submits it in an address.
    No luck with these...

    Like '*' & Chr(35) & '*'



    Like '*' & '#' & '*'

    thanks!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you enclose with square brackets

    Like '*[#]*'

    see this link for all about using wildcards https://support.office.com/en-us/art...3-61897c87b3f4

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Thanks! Much appreciated!

  4. #4
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Sorry, I had to come back to this. This doesn't work in an MS Access query. Anything inside of [] brackets is a prompt for user input.

    Like '*[#]*'

    Thanks

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps you could show us a sample of what you are looking for.
    If you just want to find a "#" in a string, this might help with syntax.

    Code:
    Sub testNumsign()
        Dim x As String
        x = "Bob Jones, #124 Smith Street, Centerville" 'sample address
        Debug.Print x, IIf(InStr(x, "#") > 0, " in position " & InStr(x, "#"), " -#-Not in x")
    End Sub
    As Ajax said for wildcards you enclose in square brackets.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    works OK for me

    so does this

    Like '*' & '[#]' & '*'

    but this will come back with a prompt

    Like '*' & [#] & '*'

  7. #7
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    This is my query. [#] Prompts for user input.

    SELECT Nz(Count([DMImports]![OrderNo])) AS OrderNoCount
    FROM Address1CheckData, [DMImports]
    WHERE (((StrConv([DMImports]![ShipToAdd1],1)) Like '*' & '#' & '*'));

    thanks

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Original:
    It appears you are trying to get the user to Enter a Parameter. This not a wild card issue as I understand your set up.
    It is not clear why you would do the StrConv ( to get upper case) since Access is case insensitive.

    It would help readers if you could show us a sample record showing the data you would query with this SQL.

    Update: After reviewing subsequent posts:
    The OP wants to select those records where the ShipToAdd1 contains an octothorpe/hash/number sign or
    possibly any number???
    Last edited by orange; 12-05-2018 at 01:36 PM. Reason: clarification

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    @Shank - you have not looked at post #6.
    What you say prompts for a parameter is not what was suggested in post #2 and is not demonstrated as being used in your post #7. If something isn't working, provide the code for what isn't working, not something that does not quite do what you want and a comment about what doesn't.

  10. #10
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    I should have elaborated in the original post. My effort is to clean up address1 field.
    Example: 12345 Anywhere St #4
    I want to search for any address1 field that contains the character #.
    I hope that clarifies.

    thanks

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I understand that - but you have not done what I asked you to do

    1. look at post 2
    2. look at post 6
    3. provide the code you are actually using that prompts you for a parameter

  12. #12
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Sorry! It would probably help if I actually read what was offered instead of glossing over it.

    This works as I wanted.

    SELECT Nz(Count([DMImports]![OrderNo])) AS OrderNoCount
    FROM [DMImports]
    WHERE (((StrConv([DMImports]![ShipToAdd1],1)) Like '*[#]*'));

    Thanks all!

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

Similar Threads

  1. wild card * or % which one to use
    By ntambomvu in forum Access
    Replies: 4
    Last Post: 12-02-2018, 06:27 PM
  2. Wild Card Issue
    By bishmedia in forum Queries
    Replies: 7
    Last Post: 05-20-2018, 08:29 AM
  3. drill down and wild card criteria
    By Jen0dorf in forum Access
    Replies: 3
    Last Post: 02-12-2016, 12:12 PM
  4. DoCmd.ApplyFilter with wild card
    By cksm4 in forum Programming
    Replies: 3
    Last Post: 10-05-2010, 08:24 AM
  5. Import with a wild card
    By ukgooner in forum Import/Export Data
    Replies: 3
    Last Post: 09-09-2009, 08:08 AM

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