Results 1 to 8 of 8
  1. #1
    williamposey is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    16

    Criteria Question for LIKE

    I have a Search form that uses a combo drop down box to find LIKE data in an associated query.
    Example: Like "*" & [Forms]![Drop Down Search Form]![Gender] & "*"
    This works fine for all data that is completely different, but it finds everything when I look up either Male or Female. This of course is because they both contain the word Male.

    If I try using:


    =[Forms]![Form1]![Gender]
    Then it forces my combo drop down to be used to pick one or the other (male or Female) . This is an issue because sometimes I need to leave the field blank to find both female and male.

    To recap:
    =[Forms]![Form1]![Gender] forces me to pick on or the other ( Male or Female)
    Like "*" & [Forms]![Drop Down Search Form]![Gender] & "*" will find both Male and female.

    Any suggestions for a criteria where I can either pick one or the other to get the specific results AND also have the option to leave the field blank to find both?

    Thanks -W

  2. #2
    williamposey is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    16
    To further clarify, My drop down combo box used for searches has choices of Male or Female. TO me it seems like there is a criteria that can look up only the first two letters of a word based on what ever variable is selected in the combo box.

  3. #3
    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
    Have you tried removing the trailing *
    Like "*" & [Forms]![Drop Down Search Form]![Gender] & "*"
    This will force a search using starting characters. It may not be what you need generally, but it will definitely distinguish Female from Male.
    Perhaps there is more to your question.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Have you tried removing the trailing *
    think you mean the preceding *?

    alternatively suggest use the criteria

    gender=([Forms]![Drop Down Search Form]![Gender] OR [Forms]![Drop Down Search Form]![Gender] is Null)

  5. #5
    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

  6. #6
    williamposey is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    16
    Thanks to you both for your assitance..

    So when I try: gender=([Forms]![Drop Down Search Form]![Gender] OR [Forms]![Drop Down Search Form]![Gender] is Null) it errors out as too complicated and tells me to simplify the criteria.

    then when I try
    Like "*" & [Forms]![Drop Down Search Form]![Gender] & "*" OR Like [Forms]![Drop Down Search Form]![Gender] it doesn't not let me search with a blank input to give me both male and female when I need.

    Am I misunderstanding you both? thanks -W

  7. #7
    williamposey is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    16
    So I went back and tried : Like [Forms]![Drop Down Search Form]![Gender] & "*" Worked like a charm. I knew I was over looking something small.

    THANK YOU BOTH SO MUCH! You are rock stars!

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Glad you've got it working but a bit confused

    for some reason, you only quote the criteria but not what field it relates to so I would expect

    gender=([Forms]![Drop Down Search Form]![Gender] OR [Forms]![Drop Down Search Form]![Gender] is Null)

    and

    gender Like [Forms]![Drop Down Search Form]![Gender] & "*"

    to both work

    so perhaps if you just tried

    =([Forms]![Drop Down Search Form]![Gender] OR [Forms]![Drop Down Search Form]![Gender] is Null)

    that would work

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

Similar Threads

  1. Criteria Query Question !
    By Guy Winfield in forum Access
    Replies: 5
    Last Post: 05-15-2015, 09:41 AM
  2. IS NULL in criteria question
    By McArthurGDM in forum Queries
    Replies: 12
    Last Post: 07-31-2014, 04:52 PM
  3. Criteria question
    By El Labrador in forum Access
    Replies: 2
    Last Post: 12-04-2013, 07:03 PM
  4. Question About Criteria
    By Kipster1203 in forum Queries
    Replies: 2
    Last Post: 05-20-2010, 12:09 PM
  5. Query Criteria Question
    By jrockusa in forum Queries
    Replies: 0
    Last Post: 11-13-2009, 09:16 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