Filter a list containing null values

W

Wendymel

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
 
T

tina

well, i'm assuming that you want the users to be able to choose a value in
only the combobox controls they want, rather than having to enter a value in
every control. try changing your query's WHERE clause to

WHERE (((tblProjectData.PROJDescription) Like "*" &
[Forms]![frmProjectList]![DescriptionSearch] & "*" Or
[Forms]![frmProjectList]![DescriptionSearch] Is Null) AND
((tblProjectNumber.ProjectNumber) Like "*" &
[Forms]![frmProjectList]![cbopProjectNumberChoice] & "*" Or
[Forms]![frmProjectList]![cbopProjectNumberChoice] Is Null) AND
((tblBudgetYear.BudgetYear)
Like "*" & [Forms]![frmProjectList]![cboBudgetYearChoice] & "*" Or
[Forms]![frmProjectList]![cboBudgetYearChoice] Is Null) AND
((tblLocation.Location) Like "*" &
[Forms]![frmProjectList]![cboLocationChoice] & "*" Or
[Forms]![frmProjectList]![cboLocationChoice]
Is Null) AND ((tblProjStatus.ProjectStatus) Like "*" &
[Forms]![frmProjectList]![cboStatusChoice] & "*" Or
[Forms]![frmProjectList]![cboStatusChoice] Is Null) AND
((tblFundingType.FundingSourceType) Like "*" &
[Forms]![frmProjectList]![cboFundingChoice] & "*" Or
[Forms]![frmProjectList]![cboFundingChoice] Is Null) AND
((tblAssignedPriority.AssignedPriorityDetail) Like "*" &
[Forms]![frmProjectList]![cboAssignedPriorityChoice] & "*" Or
[Forms]![frmProjectList]![cboAssignedPriorityChoice] Is Null) AND
((tblProjManager.ProjectManager) Like "*" &
[Forms]![frmProjectList]![cboProjectManagerChoice] & "*" Or
[Forms]![frmProjectList]![cboProjectManagerChoice] Is Null))

hth


Wendymel said:
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
Click to expand...
 
K

Ken Snell [MVP]

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 said:
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
Click to expand...
 
W

Wendymel

Thank you Ken! I knew it was something simple that I overlooked.

Wendy

Ken Snell said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filtering a subform using many combo boxes 7
Setting Values to Null or False Using a Update Query 2

Top