If NotNull then...

Posted on 2004-11-02
Medium Priority
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 59
ID: 12472240
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

ID: 12472332
And the where clause seems to be incomplete.

You get something like this:

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

Author Comment

ID: 12472337
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.

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 19

Expert Comment

by:Arno Koster
ID: 12472346
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 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 12472446

    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

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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

588 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