Filter table with combo box to look for Nulls- "Action or Method req" 2

[Poduska]

I want to add a "Null" option to my combo boxes that I use to create and apply filters.

I have a form with many combo boxes which I use to filter a table within a form. I oncatenatethe combo box values together and then apply a filter. The combo boxes are populated by a query of the approporiate field in the table. This all works fine until I select the blank item which is a null then I receive the error "Action or Method requires at least one argument"

This is part of the code which builds my filter and it works but I am left with the error. I utilize the combo box (with filter in name) tag property to pass the name of the field I will be filtering. tag = [FieldToFilter] etc.

  If Me![cboPlanOKDateFilter] <> "" Then
        If sFilter = "" Then
           sFilter = Me![cboPlanOKDateFilter].Tag & "= #" & Me![cboPlanOKDateFilter] & "#"
        Else
            
            sFilter = sFilter & " AND " & Me![cboPlanOKDateFilter].Tag & "= #" & Me![cboPlanOKDateFilter] & "#"
        End If
   Else

I tried this and it works for the null (no error) but leaves a combo box that doesn't really let the user know that a filter is being used as it is blank which is a bit confusing. When I added the code to change the combo box value with "Is Null" to tell the user it is being filtered then the filter crashes when the user picks another filter option (there are 10).

        If IsNull(Me![cboPlanOKDateFilter]) Then
            If sFilter = "" Then
               sFilter = Me![cboPlanOKDateFilter].Tag & "= null"
            Else
                sFilter = sFilter & " AND " & Me![cboPlanOKDateFilter].Tag & "= null"
            End If
           Me![cboPlanOKDateFilter] = "Is Null"
        End If
    End If

Is there as way to somehow concatenate "Null" on to my source query so the word "Null" shows in my combo box and still works in my filter? Somthing like an Excel filter

Thanks for any insight or pointers of where to look, I did a lot of searching but never found what I needed.

 

[Remou]

You cannot have =Null. Try:

sFilter = sFilter & " AND " & Me![cboPlanOKDateFilter].Tag & " is null"

 

[Poduska]

Remou, you tip was helpful but what I want is a way to add an option to my combo box that will allow the selection of "Null" along with all the items returned by the combo box source query. Currently it is just a blank option. I would like to make it as easy for my users as possible with a combo box that looks like this.

Examples of combo Boxes I use for filter generation.

What I want             What I have 

is Null                 "Just white space"
Item1                    Item1
Item2                    Item2
Item3                    Item3
etc                      etc.

I can put some elaborate code in to accomplish the null filter but I am still left with a combo box which is actually part of the filter but looks just like the other filter combo boxes in that it is blank.

Someone has mentioned to me that there was a way to add an "*" for All records to a combo box, I want to add Null.

Thanks

 

[Remou]

Is the problem that you are not sure how to add the item? If so, you can use the method for "All" that you mention:

http://www.tek-tips.com/faqs.cfm?fid=2330

Am I on the right track?

 

[Poduska]

I think this idea will populate my Combo box like I want, haven't had the chance to test yet. Then, as the data in the combo box are dates I will just need to put in an if statement to add "is null" or enclose the date with # symbols.

This might work, let you know. Thanks for the help.

 

[Poduska]

I have the Union Query to add "Is Null" to my query output but the sort order changes from Date to Text.

SELECT DISTINCTROW tblAllModels.PlanOKDate
FROM tblAllModels
UNION select "(is null)" from tblallmodels
group by tblallmodels.planokdate
ORDER BY tblAllModels.PlanOKDate ;

But get this output sorted as text

(is null)
1/1/2008
1/1/2009
1/11/2008
1/12/2008
1/15/2008
1/15/2009

If I remove the Union statement with the "is Null" the SQL sorts as dates.

Any ideas as to a way I can get my output to sort as dates as it did before the union?[/color red]

 

[PHV]

What about this ?

SELECT Format(PlanOKDate,"yyyy-mm-dd") FROM tblAllModels
UNION SELECT "(is null)" FROM tblAllModels
ORDER BY 1;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Poduska]

Wow, That's it!
I thought about using Format but not putting the year first. I wasn't thinking outside of my comfort zone of mm-dd-yyyy.

Thanks PHV.
And a STAR