Blank item in combo box doesn't find the blank records.

I am using MS Access 2003.  I have a form with a few dozen combo boxes that will generate a report according to the data chosen once a button is clicked and the filter applied.

The combo boxes are created from each field in a table.  If the field "dtmDateOfService" combo box choice is 1/1/2011 then I only get a report with records that have that "Date of Service".  If a "Date of Service" is not filled in then a blank shows in the combo box.  I don't have a problem wtih the blank since they may want to see a report with those records that have not been assigned a date yet.  I have a problem because the report is incorrect.  When I choose the blank from the combo box and apply the filter I see all of the records even though there is only one blank.  Since the blank that shows in the combo box comes from the blank in the table I don't understand why it won't find only the one record.

I have tried many different ways to come up with this blank record but nothing works.  Can someone please help?  Thanks in advance!
avlnaAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
This is the issue with letting the user leave a combobox "Blank" and expect some kind of criteria (ISNull) to be magically applied.
See here for info on why "Blank, Null, Zero...ect. terms are so confusing in this regard.
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_1910-Understanding-Null-Empty-Blank-N-A-ZLS-Nothing-Missing.html


fyed has post one solid way of doing this.

Here is another...
If you want to select (Filter) fields with "No Value", then make "No Value" a choice in the combobox...
Make sense?

For example, you have a combobox to select the Date, add a "No Data" entry to the combobox:
The row source for the combobox will be something like this:
SELECT [YourDate] FROM YourTable WHERE YourDate<>Null UNION SELECT '<No Date>' FROM YourTable

Now in your Report Open code do something like this
If YourCombo="<No Date>" Then
     DoCmd.OpenReport "YourReport", acViewPreview, , "Isnull(YourDate)"
Else
     DoCmd.OpenReport "YourReport", acViewPreview, , "YourDate=" & "#" & YourComboBox & "#"
end if

Sample attached



Access-EEQ-26940176DateFilterRep.mdb
0
 
Patmac951Commented:
Not sure I completely understand but it sounds like the filter(query) that you designed is requiring a date and if not date is entered then it is returning all records.  Can you paste the code you are using to build the filter?

Secondly if the date field is blank it should be represented by a Null field "" not spaces.  So you can modify the query that if the date combo box is blank then query the database to look for a Null field "" in the date and it should only return records that do not have a date.
0
 
Dale FyeCommented:
How are you defining the blank in the combo box, and how are you defining the SQL string for the filter?

If you don't set the default value of the combo box to be one of the "values" listed when you drop down the combo box, then the "value" of that combo box is generally NULL.  But if you are constructing a WHERE clause based on that field something like:

if Len(strCriteria) = 0 then
    strCriteria = "[Date of Service] = " & me.cboDateOfService
ELSE
    strCriteria = strCriteria & " AND ([Date of Service] = " & me.cboDateOfService & ")"
End if

Then this will be interpreted as:

[Date of Service] = NULL

But since NULL is the absence of a value, this gets evaluated as TRUE for all values of [Date of Service].  Instead, what you need to do is something like:

if Len(strCriteria) = 0 then
    if isnull(me.cboDateOfService) Then
        strCriteria = ISNULL([Date of Service])
    else
        strCriteria = "[Date of Service] = #" & me.cboDateOfService  & "#"
    end if
ELSE
    if isnull(me.cboDateOfService) then
        strCriteria = strCriteria & " AND (ISNULL([Date of Service]))"
    else
        strCriteria = strCriteria & " AND ([Date of Service] = " & me.cboDateOfService & ")"
    end if
End if

Another way to address this is to actually add options to the combo box which displays something like "Any Value" and "Blank Values", and then process those accordingly as you build your SQL criteria string.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
avlnaAuthor Commented:
The RowSource of the combo box only has the dtmDateOfService field in the query.  That will show any data that is in this field from the table.  So if the user skipped this field on the input form there is nothing in the field in the table, it's blank and that same blank shows up in the combo box when I click the dropdown button.  That becomes my choice.

There is a button that will filter all of the choices from the combo box.  Each combo box has an If, End If, like the one listed below for Date of Service and the code to open the report is at the end.

Once my choices are made and the button clicked the Filter property of the report will show my choices.  As an example:  dtmDateOfService Like "01/01/2011"

I'm not a programmer.  I found this online and was able to understand it enough to change what I needed and it worked except for the dates.  

        'Date of Service
    If Not IsNull(Me.cboDateOfService) Then
        strFilter = strFilter & " AND dtmDateOfService Like""" & Me.cboDateOfService & """ "
    End If

        'If the report is closed, open the report
    If SysCmd(acSysCmdGetObjectState, acReport, "rptNationalNetworkManagementInquiryFilter") <> acObjStateOpen Then
        DoCmd.OpenReport "rptNationalNetworkManagementInquiryFilter", acPreview, qryNationalNetworkManagementInquiryReportFilter
    End If
   
        'if report was open, use filter
    With Reports![rptNationalNetworkManagementInquiryFilter]
        .Filter = Mid(strFilter, 6)
        .FilterOn = True
    End With
0
 
Patmac951Commented:
I would follow the post from boag2000.  

If you have a query that selects date like "01/01/2011"  you have to create some code that will include null values ""  if the date combo box is blank and you are trying to find records in the database that have no date then  you have to have some code to find null values.
0
 
Dale FyeCommented:
Patmac951 said: "... that will include null values "" if the date ..."

Null values are not the same as an empty string (which is represented by "").  Do not confuse the two as they are two different species of animal.
0
 
Jeffrey CoachmanMIS LiasonCommented:
avlna,

You can certainly design your interface in any way you want.

I simply posted how I would do this to avoid the issue of leaving the box blank and having to "figure out" what criteria to assign to it.

The issue here is, again, the issue of Nothing, Null, Empty, Zero Lengthstrings...etc
Just to much inconsistencies in doing it your way.

In other words, (again) if they want to select all Blank date, then make a "All Blank dates" selection.
And set your validation to make sure a selection is made in the combobox
If Isnull(me.combo) or me.combo="" then
    msgbox "You must select a value from the combobox.", vbinformation
    exit sub
end if


JeffCoachman
0
 
avlnaAuthor Commented:
I couldn't use the  "if isnull(me.cboDateOfService) Then" statement because when the filter is removed it sets the value of the combo box to Null and that information became part of every report.

I also couldn't use SELECT [YourDate] FROM YourTable WHERE YourDate<>Null UNION SELECT '<No Date>' FROM YourTable

Now in your Report Open code do something like this
If YourCombo="<No Date>" Then
     DoCmd.OpenReport "YourReport", acViewPreview, , "Isnull(YourDate)"
Else
     DoCmd.OpenReport "YourReport", acViewPreview, , "YourDate=" & "#" & YourComboBox & "#"
end if

I need all the choices to string together with AND in case the user makes more choices from the other combo boxes.

I was able to use the '<No Data>' though.  I had tried adding text to the field but because it was a date I kept getting a message that the format was wrong.  I'm not a programmer so I didn't know the syntax for getting text into a date field.  I used this in an IIf statement in my combo box query and again in my report query.  It worked perfectly and I didn't have any other issues.

Thank you everyone for the help!
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I couldn't use the  "if isnull(me.cboDateOfService) Then" statement because when the filter is removed it sets the value of the combo box to Null and that information became part of every report.>
What filter are you talking about?
In my sample I am simply opening the Report with the WHERE argument.
There is no filter in use in the sample I posted...

This is my entire point...
...null should not be allowed in the combobox in the first place.

If you have to "Remove a filter", then set the combobox value to back to "No Date" (Or all dates or whatever...)
But simply do no allow a null to be in the combobox, (With the validation code I posted) and avoid all of your issues...

In other words, what's wrong with the sample I posted as it stands?

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.