Results 1 to 7 of 7
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Using Like in VBA

    I can assign a value to a String - Dim strUnit = "1-1 CAV SQDN" - and use that as a filter. What I would like to do is use the Like "*1-1 CAV*" type function.



    I want the code to search ......range(irow, 4) = strUnit

    Ranges I have
    1-1 CAV SQDN
    A TROOP, 1-1 CAV SQDN
    B TROOP, 1-1 CAV SQDN
    C TROOP, 1-1 CAV SQDN
    D TROOP, 1-1 CAV SQDN
    D CO (FSC), 1-1 CAV SQDN

    If I use Dim strUnit = "1-1 CAV SQDN" I only get whats = to 1-1 CAV SQDN in Column 4

    VBA doesn't care for Like.

    with Dim strUnit Like "*1-1 CAV*" I get an error.

    What is the correct way?

    I have tried StrUnit "1-1 CAV SQDN" Like "*1-1 CAV SQDN*" and StrUnit "*1-1 CAV SQDN*" Like "1-1 CAV SQDN", the out put is as if I used strUnit = "1-1 CAV SQDN"

    Can someone clarify please?

    Thanks
    Wayne

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    only queries use LIKE.
    Code:
    dim strUnit as string
    strUnit = "1-1 CAV SQDN"
    
    me.filter = "[Sqdn] like *" & strUnit & "*"
    me.filterOn = true

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks Ranman,
    I also figured out I needed to open up my row range. I had it too limited to encompass all my test changes. I tried your version, but I get an error:

    Run-Time error '3075':
    Syntax error 9missing operator0 in query expression '[SQDN] like *1-1 CAV SQDN*'. I was getting something the same when I used Dim strUnit = "1-1 CAV SQDN"

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your target needs to be in quotes, your error message seems to indicate it isn't

    [SQDN] like '*1-1 CAV SQDN*'

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Ajax,
    I was using this code as provided above.

    Dim strUnit as string
    strUnit = "1-1 CAV SQDN"
    me.filter = "[SQDN] like *" & strUnit & "*"
    me.filterOn = True

    Change me.filter = "[SQDN] like *" & strUnit & "*" to me.filter = [SQDN] like '*1-1 CAV SQDN*' won't work.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it should be

    me.filter = "[SQDN] like '*" & strUnit & "*'"

    you might find this link informative

    https://msdn.microsoft.com/en-us/lib...or=-2147217396

  7. #7
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Ajax,
    I'll check it out, Thanks.

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

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