Re: Filter a list containing null values


You need to change your WHERE clause so that you're testing for NULL in the
combo box's value, not in the table's field's value. Here is the first
grouping from your current WHERE clause:

WHERE (((tblProjectData.PROJDescription) Like "*" &
[Forms]![frmProjectList]![DescriptionSearch] & "*" Or
(tblProjectData.PROJDescription) Is Null) AND


The above needs to be changed to this:

WHERE (((tblProjectData.PROJDescription) Like "*" &
[Forms]![frmProjectList]![DescriptionSearch] & "*" Or
[Forms]![frmProjectList]![DescriptionSearch] Is Null) AND

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Make the same type of change in all the other groupings in the WHERE clause.

"Wendymel" <Wendymel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3392F20E-C2B8-43A3-9BC0-517F5E66A47C@xxxxxxxxxxxxxxxx
I have a form (frmProjectList) with a subform (frmSubProjectList)
containing
a list of all the records in the db.

In the form I have 7 unbound combo lists that are used to filter the list
either separately, or in combination. The all worked fine up until the
users
decided that some of the fields will not contain any information (null or
blank). Now when the combo boxes are used to filter the list, they
include
not only the correct records, but the records with Null or blank values in
the fields that have just been filtered.

My subform record source is a query (qryProjects). My combo boxes all use
a
row source type table/query with an after update and on change event of
DoCmd.Requery "frmSubProjectList"

For example, the subform qryProjects reads:

SELECT tblProjectData.ProjectUNID, tblProjectData.PROJDescription,
tblProjectData.PROJBudget, tblProjectData.CurrentProjAuth,
tblProjectData.ProjectNumber, tblProjectNumber.ProjectNumber,
tblProjectData.PROJBudgetYear, tblBudgetYear.BudgetYear,
tblProjectData.PROJLocation, tblLocation.Location,
tblProjectData.ProjectStatus, tblProjStatus.ProjectStatus,
tblProjectData.PROJFundingType, tblFundingType.FundingSourceType,
tblProjectData.PROJAssignedPriority,
tblAssignedPriority.AssignedPriorityDetail, tblProjectData.ProjectManager,
tblProjManager.ProjectManager
FROM tblFundingType RIGHT JOIN (tblBudgetYear RIGHT JOIN (tblProjectNumber
RIGHT JOIN (tblAssignedPriority RIGHT JOIN (tblProjStatus RIGHT JOIN
(tblProjManager RIGHT JOIN (tblLocation RIGHT JOIN tblProjectData ON
tblLocation.Location = tblProjectData.PROJLocation) ON
tblProjManager.ProjectManager = tblProjectData.ProjectManager) ON
tblProjStatus.ProjectStatus = tblProjectData.ProjectStatus) ON
tblAssignedPriority.AssignedPriority =
tblProjectData.PROJAssignedPriority)
ON tblProjectNumber.ProjectNumber = tblProjectData.ProjectNumber) ON
tblBudgetYear.BudgetYear = tblProjectData.PROJBudgetYear) ON
tblFundingType.FundingSourceType = tblProjectData.PROJFundingType
WHERE (((tblProjectData.PROJDescription) Like "*" &
[Forms]![frmProjectList]![DescriptionSearch] & "*" Or
(tblProjectData.PROJDescription) Is Null) AND
((tblProjectNumber.ProjectNumber) Like "*" &
[Forms]![frmProjectList]![cbopProjectNumberChoice] & "*" Or
(tblProjectNumber.ProjectNumber) Is Null) AND ((tblBudgetYear.BudgetYear)
Like "*" & [Forms]![frmProjectList]![cboBudgetYearChoice] & "*" Or
(tblBudgetYear.BudgetYear) Is Null) AND ((tblLocation.Location) Like "*" &
[Forms]![frmProjectList]![cboLocationChoice] & "*" Or
(tblLocation.Location)
Is Null) AND ((tblProjStatus.ProjectStatus) Like "*" &
[Forms]![frmProjectList]![cboStatusChoice] & "*" Or
(tblProjStatus.ProjectStatus) Is Null) AND
((tblFundingType.FundingSourceType) Like "*" &
[Forms]![frmProjectList]![cboFundingChoice] & "*" Or
(tblFundingType.FundingSourceType) Is Null) AND
((tblAssignedPriority.AssignedPriorityDetail) Like "*" &
[Forms]![frmProjectList]![cboAssignedPriorityChoice] & "*" Or
(tblAssignedPriority.AssignedPriorityDetail) Is Null) AND
((tblProjManager.ProjectManager) Like "*" &
[Forms]![frmProjectList]![cboProjectManagerChoice] & "*" Or
(tblProjManager.ProjectManager) Is Null))
ORDER BY tblProjectData.ProjectNumber;

An example of one of the unbound combo box filters is:

Project Manager
Row Source: SELECT ProjectManager FROM tblProjManager ORDER BY
[ProjectManager];

If a user selects a project manager from the list named JSmith then the
list
is filtered down to all projects with a project manager JSmith as well as
the
records that have no project manager (blank or null)

I do not want the null values included in the result set.

I have been beating my head against the wall on this one. The answer is
probably very simple, I just need fresh eyes to look at it.

Thanks



.

Relevant Pages

  • Re: Filter a list containing null values
    ... In the form I have 7 unbound combo lists that are used to filter the list ... SELECT ProjectManager FROM tblProjManager ORDER BY ... If a user selects a project manager from the list named JSmith then the ...
    (microsoft.public.access.forms)
  • Re: Filter a list containing null values
    ... only the combobox controls they want, rather than having to enter a value in ... In the form I have 7 unbound combo lists that are used to filter the list ... SELECT ProjectManager FROM tblProjManager ORDER BY ...
    (microsoft.public.access.forms)
  • Filter a list containing null values
    ... In the form I have 7 unbound combo lists that are used to filter the list ... SELECT ProjectManager FROM tblProjManager ORDER BY ... If a user selects a project manager from the list named JSmith then the list ...
    (microsoft.public.access.forms)
  • New, revised list of spyware-related IPs
    ... replaced those filters with complete filters of Internetfuel. ... This IP blocklist is intended to be used to block known spyware sites ... This will filter Alexa while ... able to delete rules in the previous lists which block traffic to and ...
    (comp.security.firewalls)
  • RE: Connecting and filtering lists
    ... I had set the filter up to filter that ... >> We added two User Information Lookup columns to the TeamMembers Table. ... >> then the MemberProject, then the Projects table. ... >> PROJECTS views show all rows from the lists (they are only filtered after the ...
    (microsoft.public.sharepoint.portalserver.development)