Results 1 to 12 of 12
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167

    "contains" filter

    Can someone tell me the proper express to use if i want to filter a field for a record that CONTAINS the info the user provides.


    for instance user enters : ma
    filter returns: may / march and any other record with the "MA" string

    currently i'm using :
    [STATUS]=Forms![fLookup]![Combo38]

    can you show me how to adjust this?

    thanks in advance

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ??/ not sure I understand your
    Code:
    [STATUS]=Forms![fLookup]![Combo38]
    But, let's say the user selects "XY" in combo38, then you could have code

    Code:
     If instr(Status, "XY") > 0 then  ' does "XY" occur within Status
      ---true                                  '  returns the starting position of XY within Status
    else
      ---false                                 '  not found
    end if
    See this for more on InStr
    Last edited by orange; 03-13-2020 at 07:44 AM. Reason: spelling

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    You could also maybe use the Like condition.

    https://www.techonthenet.com/access/queries/like.php

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    filter returns: may / march and any other record with the "MA" string
    need some clarification on what this actually means.

    You reference months - but if that is supposed to be a date e.g. 11th May 2020, dates are numeric (they are just formatted as you require them) so you will never find May or March.

    And any other record? is that also any field? or is it just a single column?

    currently i'm using :
    [STATUS]=Forms![fLookup]![Combo38]
    can you show me how to adjust this?
    code to set your filter would be

    me.filter="Status Like '*" & Forms![fLookup]![Combo38] & "*'"
    me.filteron=true

    assuming your combo is on the form you are filtering, you only need

    me.filter="Status Like '*" & me.[Combo38] & "*'"
    me.filteron=true

  5. #5
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Sorry for not being more specific.

    currently in the WHERE CONDITION of the OPEN FORM "new action" i'm using [STATUS]=Forms![fLookup]![Combo38]
    This works fine but i'm looking for the expression that will allow the user to put a few characters in the [Combo38] field and get all the results that contain that string.



  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK so your where condition would be

    "Status Like '*" & Forms![fLookup]![Combo38] & "*'"


  7. #7
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    this isn't working Ajax. do i replace the whole line:

    [STATUS]=[Forms]![fLookup]![Combo38]

    with

    "Status Like '*" & Forms![fLookup]![Combo38] & "*'"

    i tried this and it doesn't work. any other suggestions?

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of your database - doesn't have to be all the data? Just enough to show the issue.
    Or at least all of the code in the procedure.
    Doesn't work doesn't help. What happens -details?

  9. #9
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    does a copy of the macro screen help?
    Attached Thumbnails Attached Thumbnails Capture1.JPG  

  10. #10
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Click image for larger version. 

Name:	Capture.JPG 
Views:	13 
Size:	23.2 KB 
ID:	41296does a copy of the macro screen help?

    sorry this is the correct image i meant to attach.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I do not use macros so can not give focused response. I did find this re macro applyfilter from M$oft.


    Can you post a copy of the database so readers can do some experimenting an get you a working solution?

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't either - hadn't noticed which sub forum this was posted under.

    My best guess would be

    [Status] Like "*" & Forms![fLookup]![Combo38] & "*"

    but will drop out now as I don't believe I can offer any further help

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

Similar Threads

  1. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  2. Replies: 1
    Last Post: 02-28-2017, 12:29 PM
  3. Replies: 3
    Last Post: 07-07-2016, 12:22 PM
  4. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  5. Replies: 4
    Last Post: 01-22-2015, 10:30 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