If NotNull then...

Posted on 2004-11-02
Last Modified: 2007-12-19
I have simple search for where the user checks a check box next to the criteria that they would like to filter by.  But I was wonder if there was a way to remove the check box.  I have tried If Not IsNull but it does not work.  Here is the code that I use.

    If chkLegal = True Then
    If Nz(Forms![frm_Customer_Search]!txtLegalName, "") <> "" Then
        strWhere = "(Legal_Name LIKE '*" & Replace(Forms![frm_Customer_Search]!
        txtLegalName, "'", "''")  & "*') AND "
    End If
    End If
Question by:garland29
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    Hi garland29,

      Not quite sure what your asking here.  Are you saying you'd like to remove the check box (chkLegal) and just use the txtLegalName control?  

    LVL 16

    Expert Comment

    And the where clause seems to be incomplete.

    You get something like this:

    strWhere = "(Legal_name Like '*yourvalue*') AND"

    Author Comment

    Yes I would like to remove the check box.  But there are about 8 text boxes with different criteria. I would like it to check if there is anything in the text box if not then move on.

    LVL 19

    Expert Comment

    so you'd want to filter on a criteria if and only if the criteria is not empty ?


        returns the length of the txtLegalName field.

    if len(txtLegalName) > 0 then
    end if

    or you could use IsEmpty( ), IsNull( ), IsNumeric( ) etc
    LVL 56

    Accepted Solution


        This is the way I usually build my WHERE clauses

        Dim strCriteria

        strWhere = ""

        If Nz(Forms![frm_Customer_Search]!txtLegalName, "") <> "" Then
            strWhere  = strWhere & "Legal_Name LIKE '*' " &  Forms![frm_Customer_Search]![txtLegalName] & " AND"
       End If

       ' Next check

       ' Strip off last AND
      If Len(strWhere)>0 then strWhere =Left$(strWhere,Len(strWhere)-4))


    Author Comment

    I love the ones that are staring right back at me;-)  Thanks!!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Prepare to Pass the CompTIA A+ 900 Series Exam

    CompTIA aims to adapt its A+ Certification to reflect the most current knowledge and skills needed by today's IT professionals--and this year's 2016 exam is harder than ever. This certification is one of the most highly-respected and sought after in IT.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now