Results 1 to 3 of 3
  1. #1
    laubox is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2014
    Posts
    1

    If Statement for search form criteria


    Hi,

    I have created a multi field search form, which executes a query based on the information entered in the search form.
    In my query, the search criteria for each field I am searching on is Like "*" & [Forms!]![SearchForm]![fieldname1] & "*".

    My table which my query pulls from may have blank cells in the fields which I am searching on. I have 6 fields in my search form.

    When I only enter search criteria in the search form for some of the fields (for example 4 of the 6 fields) it doesn't return the records which match the criteria that I have entered if the 4 fields of my search form, if the cells in my table are empty for one of the other 2 fields pn the search form for which I did not enter anything.

    I am trying to use an if statement as the search criteria to say IF the search form box is NOT NULL, then Search for Like "*" & [Forms!]![SearchForm]![fieldname1] & "*". but IF the search form box is NULL, then don't use the search form as a criteria but return all records.

    Can anybody help with the syntax for this IF statement?

    Any insight or help would be really appreciated!
    Thanks.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    see this free video for ideas. It was done with Access 2000-2003, but the concept is the critical part.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    That tutorial presents an approach that assumes every record will have data in the criteria fields.

    If null is possible, must handle. Options:

    1. Create fields in query with expression to convert null and apply wildcard filter criteria to that field
    [fieldname] & ""
    or
    Nz([fieldname], "none")

    2. Use Or Is Null as part of the criteria parameter
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. If Statement Criteria Needed for form
    By burrina in forum Forms
    Replies: 4
    Last Post: 12-06-2012, 10:47 PM
  2. If Statement Criteria Needed for form
    By burrina in forum Forms
    Replies: 6
    Last Post: 11-16-2012, 11:00 PM
  3. Search form criteria
    By atom in forum Forms
    Replies: 3
    Last Post: 04-03-2012, 06:42 AM
  4. Multiple Search Criteria Form
    By J77TDC in forum Forms
    Replies: 3
    Last Post: 08-09-2011, 08:50 AM
  5. Passing Criteria to IN statement from Form
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-09-2009, 04:02 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