Dcount multiple Criteria, search on multiple fields

I want to exclude a field from the search if it is null

the code I gave you tests each control to see if it has a value and only includes if it does have one.

You need to look at the code and make appropriate substitutions for your controlnames and fieldnames

I am happy to help YOU do it ~

thanks, not asking you to do it for me.  I was confused by your initial statement "rather than using wildcards, which will exclude values in fields that are Null"

I will take a stab at it and let you know.  Thanks!

great!

>rather than using wildcards, which will exclude 'values' in fields that are Null  
By that I meant that oftentime, folks think a wildcard will pick up anything but it won't because it won't get nothing. Additionally, it is not efficient either.

If you have questions ... ask.  Look at the table design to check the data types that are stored.

Ok, I took a stab at it.  I tried the following code, and I am getting a syntax error on the first If not IsNull line, in fact all of those lines show up in red in the VBA editor.  Where am I off?

Function AdvSearchBox()

   Dim varFilter As Variant
   varFilter = Null
 
   If not IsNull[Forms]![Advanced_Search]![PLU]  Then
      varFilter = (varFilter + " AND ") _
         & "[PLU] = '" & [Forms]![Advanced_Search]![PLU] & "'"
   End If
 
   If not IsNull [Forms]![Advanced_Search]![Description] Then
      varFilter = (varFilter + " AND ") _
         & "[Description] = '" & [Forms]![Advanced_Search]![Description] & "'"
   End If
 
  If not IsNull [Forms]![Advanced_Search]![Size] Then
      varFilter = (varFilter + " AND ") _
         & "[Size] = '" & [Forms]![Advanced_Search]![Size] & "'"
   End If
 
   With Me
       If Not IsNull(varFilter) Then
          [Forms]![Advanced_Search].Filter = varFilter
          [Forms]![Advanced_Search].FilterOn = True
      Else
          [Forms]![Advanced_Search].FilterOn = False
      End If
   End With
 
 End Function

where is the code? It should be behind the form with the criteria so instead of, for instance, If not IsNull[Forms]![Advanced_Search]![PLU]  Then, it should be

If not IsNull(Me.PLU)  Then

Select allOpen in new window

IsNull is a function and what it is evaluating needs to be in parentheses.
This:
   With Me
       If Not IsNull(varFilter) Then
          [Forms]![Advanced_Search].Filter = varFilter
          [Forms]![Advanced_Search].FilterOn = True
      Else
          [Forms]![Advanced_Search].FilterOn = False
      End If
is probably not right either. Is the filter being applied to another form other than the one the code is behind?

I am keeping the modules separate from the forms,

Ok,  so to put this into context,  This is a pop-up search box form.  When I enter criteria in this form and hit search, it will filter on the bottom half of an existing split form called Item_Master.  

Attached is a screen shot of how I want the filter to behave vs what is currently happening with the code I am using below.


when I use the code below, it looks like it overwrites/clears out the record:

Function AdvSearchBox()

   Dim varFilter As Variant
   varFilter = Null
 
   If Not IsNull([Forms]![Advanced_Search]![PLU]) Then
      varFilter = (varFilter + " AND ") _
         & "[PLU] = '" & [Forms]![Advanced_Search]![PLU] & "'"
   End If
 
   If Not IsNull([Forms]![Advanced_Search]![Description]) Then
      varFilter = (varFilter + " AND ") _
         & "[Description] = '" & [Forms]![Advanced_Search]![Description] & "'"
   End If
 
  If Not IsNull([Forms]![Advanced_Search]![Size]) Then
      varFilter = (varFilter + " AND ") _
         & "[Size] = '" & [Forms]![Advanced_Search]![Size] & "'"
   End If
 
   With [Forms]![Item_Master]
       If Not IsNull(varFilter) Then
          [Forms]![Item_Master].Filter = varFilter
          [Forms]![Item_Master].FilterOn = True
      Else
          [Forms]![Advanced_Search].FilterOn = False
      End If
   End With
 
 End Function
C--Users-josht-Desktop-WhatIwanttoh.docx
C--Users-josht-Desktop-currently-ha.docx

> "I am keeping the modules separate from the forms"

it is best to process the criteria behind the form it is entered into.  You can send the result to another procedure.  All you have to do is change Private to Public if you want to call it from somewhere else -- but I suspect you won't need to do that.

> "split form"
easier to control forms that are not split -- but if you want to keep split form, try using "Me" instead of absolute form reference.  Personally, I don't use them.

Make a popup form with comboboxes that is unbound for specifying criteria

Rather than posting Word Documents, which we cannot test, make a sample database with sample records

It is filtering on exact match only.  Which goes back to the wild card method in my first post/qeustion.

so...  if I type the following:

field   value
PLU    30

Description  Apples


It should filter every item in the table that has a PLU with "30" and Description "Apples".  Not just an exact match.  attached is a word doc of how I want it to work.   In the word doc example I typed 411 in the pop-up form and in the bottom subform it should filter everything with a 411 in it.  

I want to do it myself so I won't attach a sample db.
C--Users-josht-Desktop-advancedfilt.docx

> "I want to do it myself "

great! Word docs don't help though -- better to just explain in the post if you are not going to post a sample database.

Is the filter being applied to another form other than the one the code is behind?

Please take care to answer the questions asked, thanks

btw, if you want to put code in a general module that is fine, and good practice! However, when the code is specific to a particular form such as what you have here, it is best to put it behind the form.

I figured it out myself!

Function AdvSearchBox()

   Dim varFilter As Variant
   varFilter = Null
 
   If Not IsNull([Forms]![Advanced_Search]![PLU]) Then
      varFilter = (varFilter + " AND ") _
         & "[PLU] Like '*" & [Forms]![Advanced_Search]![PLU] & "*'"
   End If
 
   If Not IsNull([Forms]![Advanced_Search]![Description]) Then
      varFilter = (varFilter + " AND ") _
         & "[Description] Like '*" & [Forms]![Advanced_Search]![Description] & "*'"
   End If
 
  If Not IsNull([Forms]![Advanced_Search]![Size]) Then
      varFilter = (varFilter + " AND ") _
         & "[Size] Like '*" & [Forms]![Advanced_Search]![Size] & "*'"
   End If
 
   With [Forms]![Item_Master]
       If Not IsNull(varFilter) Then
          [Forms]![Item_Master].Filter = varFilter
          [Forms]![Item_Master].FilterOn = True
      Else
         MsgBox ("Here we are in Else")

          [Forms]![Advanced_Search].FilterOn = False
      End If
   End With
 
 End Function

> "I figured it out myself!"

awesome !!! ... and congratulations!