Filter a column with blank values

[Wendymel]

Help! I have created a form with 5 seperate combo boxes designed to filter a
list in a subform either seperately or in combination. Three of them work
fine, but the other two do not. The problem is that the value could
potentially be blank in the column the combo box is set up to recognize.

For example:

Combo box name: cboProjectNumberChoice
Row Source: SELECT ProjectNumber FROM tblProjectNumber
ORDER BY [ProjectNumber];

tblProjectNumber contains all current project number in the DB with the the
user to add a new one. The problem is that a record can be added to the DB
and the Project Number may not be assigned for weeks after. Therefore the
record will show up in the list, but may not have a Project Number.

I still want the user to be able to filter the list by Project Number and I
can not get it to work. The filter does nothing.

All the working filters have the same type of row source in them. For
example the location combo box has a working filter:

Combo box name: cboLocationChoice
Row Source: SELECT Location FROM tblLocation ORDER BY [Location];


The subform is generated by a record source that is a Query. The query has
a condition in it for the Location that looks like this:

IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])

I have tried every kind of condition I know of to get the ProjectNumber
filter to work. Can anyone help me? I have a deadline of the end of this
week.

Thanks!

 

[BruceM]

To eliminate null values, add a WHERE clause:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber Is Not Null
ORDER BY [ProjectNumber]

If the lack of a project number is a 0 or a zero-length string rather than
Null you could test accordingly:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber <> 0
ORDER BY [ProjectNumber]

SELECT ProjectNumber
FROM tblProjectNumber
WHERE Len(ProjectNumber) > 0
ORDER BY [ProjectNumber]

If I misunderstand your intention, you could instead use the Nz function to
replace Null with a value:

SELECT Nz(ProjectNumber,0) FROM tblProjectNumber
ORDER BY ProjectNumber

Without knowing how you are using the ProjectNumber value in the filter or
whether you want any records returned if PorjectNumber is null I cannot
evaluate whether the suggestion will work, but if not it may point you in
the right direction.

 

[Wendymel]

Thank you Bruce for your prompt response. I tried all of your suggestions to
no avail. Let me try to explain the purpose.

I have a list of records with numerous columns of information displayed in a
subform (frmSubProjectList) that is contained within a form (frmProjectList).
All of the columns of all of the records accept two (ProjectNumber) and
(ProjectManager) are required fields on Data Entry. Therefore, the number
and manager fields may be blank for a while after the record is entered.

So.... if I am using the form (frmProjectList) to display specific records,
I would filter. If I want to filter by, lets say, location for example, I
would select a location from a combo box and the list in the subform would
reduce to display only those records that have my selected location in them.
As stated above, the locations column is one of those that is required so all
the records have this field populated.

Having said that, I now want to filter the entire list of records to show,
lets say, only the records that have project number 98765 in the project
number field. I would select this number (98765) from the combo box and what
should happen is the same as in the location example, it should reduce to
only those related records. Unfortunately, the project number field is not
required on data entry, so some of the records have a blank (Null) value, and
some of them have a number. I think this is why I can not get the filter to
work. That is my intention.

I have the field ProjectNumber set to data type text in the main table
(tblProjectData), and the project number table (tblProjNumber). I have the
relationship set to "One-to-Many". In both tables, the object has no set
format, no mask, no default value, and no validation, and "allow zero length"
is set to "yes", just very straight forward.

The Project Number combo box is unbound and the name is
cboProjectNumberChoice

and the Row Source is

SELECT ProjectNumber FROM tblProjectNumber ORDER BY [ProjectNumber];

I have the same configuration for all the other combo boxes that work.

The sub form (fromSubProjectList) uses a query (qryProjects) as a record
source.

The qryProjects has several criteria in its were clause. Here is an
example, in part:

WHERE ....
.....AND
((tblLocation.Location)=IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])) .....

This is for the location example above. I tried the same type of criteria
using the Is Not Null or the Nz and niether work.

Again, thank you for your prompt response. I appreciate anything anyone can
do to help. Let me know if you need any more specific info.

Wendy

To eliminate null values, add a WHERE clause:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber Is Not Null
ORDER BY [ProjectNumber]

If the lack of a project number is a 0 or a zero-length string rather than
Null you could test accordingly:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber <> 0
ORDER BY [ProjectNumber]

SELECT ProjectNumber
FROM tblProjectNumber
WHERE Len(ProjectNumber) > 0
ORDER BY [ProjectNumber]

If I misunderstand your intention, you could instead use the Nz function to
replace Null with a value:

SELECT Nz(ProjectNumber,0) FROM tblProjectNumber
ORDER BY ProjectNumber

Without knowing how you are using the ProjectNumber value in the filter or
whether you want any records returned if PorjectNumber is null I cannot
evaluate whether the suggestion will work, but if not it may point you in
the right direction.

Help! I have created a form with 5 seperate combo boxes designed to
filter a
list in a subform either seperately or in combination. Three of them work
fine, but the other two do not. The problem is that the value could
potentially be blank in the column the combo box is set up to recognize.

For example:

Combo box name: cboProjectNumberChoice
Row Source: SELECT ProjectNumber FROM tblProjectNumber
ORDER BY [ProjectNumber];

tblProjectNumber contains all current project number in the DB with the
the
user to add a new one. The problem is that a record can be added to the
DB
and the Project Number may not be assigned for weeks after. Therefore the
record will show up in the list, but may not have a Project Number.

I still want the user to be able to filter the list by Project Number and
I
can not get it to work. The filter does nothing.

All the working filters have the same type of row source in them. For
example the location combo box has a working filter:

Combo box name: cboLocationChoice
Row Source: SELECT Location FROM tblLocation ORDER BY [Location];


The subform is generated by a record source that is a Query. The query
has
a condition in it for the Location that looks like this:

IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])

I have tried every kind of condition I know of to get the ProjectNumber
filter to work. Can anyone help me? I have a deadline of the end of this
week.

Thanks!

 

[BruceM]

Null and zero-length strings are not at all the same thing, even though both
have the same result when viewing the data in that you do not see anything.
More here:
http://allenbrowne.com/casu-11.html

There is little reason to change "Allow zero length" to Yes.

How are you applying the filter? In what way does it not work? You mention
filtering when you select from, say, the ProjectList combo box. Is there a
combo box After Update event that causes the filtering to occur? Something
needs to happen, as the act of selecting a value from a combo box will not
by itself cause anything to happen.

Thank you Bruce for your prompt response. I tried all of your suggestions
to
no avail. Let me try to explain the purpose.

I have a list of records with numerous columns of information displayed in
a
subform (frmSubProjectList) that is contained within a form
(frmProjectList).
All of the columns of all of the records accept two (ProjectNumber) and
(ProjectManager) are required fields on Data Entry. Therefore, the number
and manager fields may be blank for a while after the record is entered.

So.... if I am using the form (frmProjectList) to display specific
records,
I would filter. If I want to filter by, lets say, location for example, I
would select a location from a combo box and the list in the subform would
reduce to display only those records that have my selected location in
them.
As stated above, the locations column is one of those that is required so
all
the records have this field populated.

Having said that, I now want to filter the entire list of records to show,
lets say, only the records that have project number 98765 in the project
number field. I would select this number (98765) from the combo box and
what
should happen is the same as in the location example, it should reduce to
only those related records. Unfortunately, the project number field is
not
required on data entry, so some of the records have a blank (Null) value,
and
some of them have a number. I think this is why I can not get the filter
to
work. That is my intention.

I have the field ProjectNumber set to data type text in the main table
(tblProjectData), and the project number table (tblProjNumber). I have
the
relationship set to "One-to-Many". In both tables, the object has no set
format, no mask, no default value, and no validation, and "allow zero
length"
is set to "yes", just very straight forward.

The Project Number combo box is unbound and the name is
cboProjectNumberChoice

and the Row Source is

SELECT ProjectNumber FROM tblProjectNumber ORDER BY [ProjectNumber];

I have the same configuration for all the other combo boxes that work.

The sub form (fromSubProjectList) uses a query (qryProjects) as a record
source.

The qryProjects has several criteria in its were clause. Here is an
example, in part:

WHERE ....
....AND
((tblLocation.Location)=IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice]))
.....

This is for the location example above. I tried the same type of criteria
using the Is Not Null or the Nz and niether work.

Again, thank you for your prompt response. I appreciate anything anyone
can
do to help. Let me know if you need any more specific info.

Wendy

To eliminate null values, add a WHERE clause:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber Is Not Null
ORDER BY [ProjectNumber]

If the lack of a project number is a 0 or a zero-length string rather
than
Null you could test accordingly:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber <> 0
ORDER BY [ProjectNumber]

SELECT ProjectNumber
FROM tblProjectNumber
WHERE Len(ProjectNumber) > 0
ORDER BY [ProjectNumber]

If I misunderstand your intention, you could instead use the Nz function
to
replace Null with a value:

SELECT Nz(ProjectNumber,0) FROM tblProjectNumber
ORDER BY ProjectNumber

Without knowing how you are using the ProjectNumber value in the filter
or
whether you want any records returned if PorjectNumber is null I cannot
evaluate whether the suggestion will work, but if not it may point you in
the right direction.

Help! I have created a form with 5 seperate combo boxes designed to
filter a
list in a subform either seperately or in combination. Three of them
work
fine, but the other two do not. The problem is that the value could
potentially be blank in the column the combo box is set up to
recognize.

For example:

Combo box name: cboProjectNumberChoice
Row Source: SELECT ProjectNumber FROM tblProjectNumber
ORDER BY [ProjectNumber];

tblProjectNumber contains all current project number in the DB with the
the
user to add a new one. The problem is that a record can be added to
the
DB
and the Project Number may not be assigned for weeks after. Therefore
the
record will show up in the list, but may not have a Project Number.

I still want the user to be able to filter the list by Project Number
and
I
can not get it to work. The filter does nothing.

All the working filters have the same type of row source in them. For
example the location combo box has a working filter:

Combo box name: cboLocationChoice
Row Source: SELECT Location FROM tblLocation ORDER BY [Location];


The subform is generated by a record source that is a Query. The query
has
a condition in it for the Location that looks like this:

IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])

I have tried every kind of condition I know of to get the ProjectNumber
filter to work. Can anyone help me? I have a deadline of the end of
this
week.

Thanks!

 

[John W. Vinson]

The subform is generated by a record source that is a Query. The query has
a condition in it for the Location that looks like this:

IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])

I have tried every kind of condition I know of to get the ProjectNumber
filter to work. Can anyone help me? I have a deadline of the end of this
week.

Try this instead as a criterion:

=[Forms]![frmProjectList]![cboLocationChoice] OR
[Forms]![frmProjectList]![cboLocationChoice] IS NULL

 

[Wendymel]

I'm sorry Bruce. This WOULD be important.....there is an "After Update"
Event Procedure as follows:

Private Sub cboProjectNumberChoice_AfterUpdate()
DoCmd.Requery "frmSubProjectList"
End Sub


As well as an "On Change" Event Procedure as follows :

Private Sub cboLocationChoice_Change()
DoCmd.Requery "frmSubProjectList"
End Sub

Thanks!

Wendy

Null and zero-length strings are not at all the same thing, even though both
have the same result when viewing the data in that you do not see anything.
More here:
http://allenbrowne.com/casu-11.html

There is little reason to change "Allow zero length" to Yes.

How are you applying the filter? In what way does it not work? You mention
filtering when you select from, say, the ProjectList combo box. Is there a
combo box After Update event that causes the filtering to occur? Something
needs to happen, as the act of selecting a value from a combo box will not
by itself cause anything to happen.

Thank you Bruce for your prompt response. I tried all of your suggestions
to
no avail. Let me try to explain the purpose.

I have a list of records with numerous columns of information displayed in
a
subform (frmSubProjectList) that is contained within a form
(frmProjectList).
All of the columns of all of the records accept two (ProjectNumber) and
(ProjectManager) are required fields on Data Entry. Therefore, the number
and manager fields may be blank for a while after the record is entered.

So.... if I am using the form (frmProjectList) to display specific
records,
I would filter. If I want to filter by, lets say, location for example, I
would select a location from a combo box and the list in the subform would
reduce to display only those records that have my selected location in
them.
As stated above, the locations column is one of those that is required so
all
the records have this field populated.

Having said that, I now want to filter the entire list of records to show,
lets say, only the records that have project number 98765 in the project
number field. I would select this number (98765) from the combo box and
what
should happen is the same as in the location example, it should reduce to
only those related records. Unfortunately, the project number field is
not
required on data entry, so some of the records have a blank (Null) value,
and
some of them have a number. I think this is why I can not get the filter
to
work. That is my intention.

I have the field ProjectNumber set to data type text in the main table
(tblProjectData), and the project number table (tblProjNumber). I have
the
relationship set to "One-to-Many". In both tables, the object has no set
format, no mask, no default value, and no validation, and "allow zero
length"
is set to "yes", just very straight forward.

The Project Number combo box is unbound and the name is
cboProjectNumberChoice

and the Row Source is

SELECT ProjectNumber FROM tblProjectNumber ORDER BY [ProjectNumber];

I have the same configuration for all the other combo boxes that work.

The sub form (fromSubProjectList) uses a query (qryProjects) as a record
source.

The qryProjects has several criteria in its were clause. Here is an
example, in part:

WHERE ....
....AND
((tblLocation.Location)=IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice]))
.....

This is for the location example above. I tried the same type of criteria
using the Is Not Null or the Nz and niether work.

Again, thank you for your prompt response. I appreciate anything anyone
can
do to help. Let me know if you need any more specific info.

Wendy

To eliminate null values, add a WHERE clause:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber Is Not Null
ORDER BY [ProjectNumber]

If the lack of a project number is a 0 or a zero-length string rather
than
Null you could test accordingly:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber <> 0
ORDER BY [ProjectNumber]

SELECT ProjectNumber
FROM tblProjectNumber
WHERE Len(ProjectNumber) > 0
ORDER BY [ProjectNumber]

If I misunderstand your intention, you could instead use the Nz function
to
replace Null with a value:

SELECT Nz(ProjectNumber,0) FROM tblProjectNumber
ORDER BY ProjectNumber

Without knowing how you are using the ProjectNumber value in the filter
or
whether you want any records returned if PorjectNumber is null I cannot
evaluate whether the suggestion will work, but if not it may point you in
the right direction.

Help! I have created a form with 5 seperate combo boxes designed to
filter a
list in a subform either seperately or in combination. Three of them
work
fine, but the other two do not. The problem is that the value could
potentially be blank in the column the combo box is set up to
recognize.

For example:

Combo box name: cboProjectNumberChoice
Row Source: SELECT ProjectNumber FROM tblProjectNumber
ORDER BY [ProjectNumber];

tblProjectNumber contains all current project number in the DB with the
the
user to add a new one. The problem is that a record can be added to
the
DB
and the Project Number may not be assigned for weeks after. Therefore
the
record will show up in the list, but may not have a Project Number.

I still want the user to be able to filter the list by Project Number
and
I
can not get it to work. The filter does nothing.

All the working filters have the same type of row source in them. For
example the location combo box has a working filter:

Combo box name: cboLocationChoice
Row Source: SELECT Location FROM tblLocation ORDER BY [Location];


The subform is generated by a record source that is a Query. The query
has
a condition in it for the Location that looks like this:

IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])

I have tried every kind of condition I know of to get the ProjectNumber
filter to work. Can anyone help me? I have a deadline of the end of
this
week.

Thanks!

 

[Wendymel]

Also, to answer more of your question, once the value is selected in the
combo box, (in a working combo box), the list reduces to only display the
records with that value. In the case of the combo boxes that are not
working, nothing happens at all. I still see the full list of
records....unfiltered.

Wendy

Null and zero-length strings are not at all the same thing, even though both
have the same result when viewing the data in that you do not see anything.
More here:
http://allenbrowne.com/casu-11.html

There is little reason to change "Allow zero length" to Yes.

How are you applying the filter? In what way does it not work? You mention
filtering when you select from, say, the ProjectList combo box. Is there a
combo box After Update event that causes the filtering to occur? Something
needs to happen, as the act of selecting a value from a combo box will not
by itself cause anything to happen.

Thank you Bruce for your prompt response. I tried all of your suggestions
to
no avail. Let me try to explain the purpose.

I have a list of records with numerous columns of information displayed in
a
subform (frmSubProjectList) that is contained within a form
(frmProjectList).
All of the columns of all of the records accept two (ProjectNumber) and
(ProjectManager) are required fields on Data Entry. Therefore, the number
and manager fields may be blank for a while after the record is entered.

So.... if I am using the form (frmProjectList) to display specific
records,
I would filter. If I want to filter by, lets say, location for example, I
would select a location from a combo box and the list in the subform would
reduce to display only those records that have my selected location in
them.
As stated above, the locations column is one of those that is required so
all
the records have this field populated.

Having said that, I now want to filter the entire list of records to show,
lets say, only the records that have project number 98765 in the project
number field. I would select this number (98765) from the combo box and
what
should happen is the same as in the location example, it should reduce to
only those related records. Unfortunately, the project number field is
not
required on data entry, so some of the records have a blank (Null) value,
and
some of them have a number. I think this is why I can not get the filter
to
work. That is my intention.

I have the field ProjectNumber set to data type text in the main table
(tblProjectData), and the project number table (tblProjNumber). I have
the
relationship set to "One-to-Many". In both tables, the object has no set
format, no mask, no default value, and no validation, and "allow zero
length"
is set to "yes", just very straight forward.

The Project Number combo box is unbound and the name is
cboProjectNumberChoice

and the Row Source is

SELECT ProjectNumber FROM tblProjectNumber ORDER BY [ProjectNumber];

I have the same configuration for all the other combo boxes that work.

The sub form (fromSubProjectList) uses a query (qryProjects) as a record
source.

The qryProjects has several criteria in its were clause. Here is an
example, in part:

WHERE ....
....AND
((tblLocation.Location)=IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice]))
.....

This is for the location example above. I tried the same type of criteria
using the Is Not Null or the Nz and niether work.

Again, thank you for your prompt response. I appreciate anything anyone
can
do to help. Let me know if you need any more specific info.

Wendy

To eliminate null values, add a WHERE clause:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber Is Not Null
ORDER BY [ProjectNumber]

If the lack of a project number is a 0 or a zero-length string rather
than
Null you could test accordingly:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber <> 0
ORDER BY [ProjectNumber]

SELECT ProjectNumber
FROM tblProjectNumber
WHERE Len(ProjectNumber) > 0
ORDER BY [ProjectNumber]

If I misunderstand your intention, you could instead use the Nz function
to
replace Null with a value:

SELECT Nz(ProjectNumber,0) FROM tblProjectNumber
ORDER BY ProjectNumber

Without knowing how you are using the ProjectNumber value in the filter
or
whether you want any records returned if PorjectNumber is null I cannot
evaluate whether the suggestion will work, but if not it may point you in
the right direction.

Help! I have created a form with 5 seperate combo boxes designed to
filter a
list in a subform either seperately or in combination. Three of them
work
fine, but the other two do not. The problem is that the value could
potentially be blank in the column the combo box is set up to
recognize.

For example:

Combo box name: cboProjectNumberChoice
Row Source: SELECT ProjectNumber FROM tblProjectNumber
ORDER BY [ProjectNumber];

tblProjectNumber contains all current project number in the DB with the
the
user to add a new one. The problem is that a record can be added to
the
DB
and the Project Number may not be assigned for weeks after. Therefore
the
record will show up in the list, but may not have a Project Number.

I still want the user to be able to filter the list by Project Number
and
I
can not get it to work. The filter does nothing.

All the working filters have the same type of row source in them. For
example the location combo box has a working filter:

Combo box name: cboLocationChoice
Row Source: SELECT Location FROM tblLocation ORDER BY [Location];


The subform is generated by a record source that is a Query. The query
has
a condition in it for the Location that looks like this:

IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])

I have tried every kind of condition I know of to get the ProjectNumber
filter to work. Can anyone help me? I have a deadline of the end of
this
week.

Thanks!

 

[BruceM]

I'm not sure I have the names and other details straight, but if the combo
box is on the main form, and if you use criteria for the query is as John
Vinson described to allow for Null, your combo box After Update event could
be:

Me.frmSubProjectList.Requery

I think the DoCmd syntax you used would work also, but I tend not to use
that when other options are available. In any case, you need to requery the
subform after making the combo box selection. I wouldn't use the Change
event, which occurs after each character is entered. I think it would work
also after making a selection from the combo box, but the After Update event
is probably the best place for the code. If you have thousands of record in
the list there are ways of using the combo box Change event to narrow down
the list as you type, but that would involve more than the one line of code
you are showing.

You may want to be sure to clear combo boxes you are not using for the
filtering. If you have criteria in the query for five combo boxes you may
find the results are more restricted than you intended. For instance, if
you are filtering by job number and location, but you choose a location that
does not include the selected job number, your query will return zero
records. The details of how to go about this would vary depending on
whether you wish to filter by several criteria at once.

I'm sorry Bruce. This WOULD be important.....there is an "After Update"
Event Procedure as follows:

Private Sub cboProjectNumberChoice_AfterUpdate()
DoCmd.Requery "frmSubProjectList"
End Sub


As well as an "On Change" Event Procedure as follows :

Private Sub cboLocationChoice_Change()
DoCmd.Requery "frmSubProjectList"
End Sub

Thanks!

Wendy

Null and zero-length strings are not at all the same thing, even though
both
have the same result when viewing the data in that you do not see
anything.
More here:
http://allenbrowne.com/casu-11.html

There is little reason to change "Allow zero length" to Yes.

How are you applying the filter? In what way does it not work? You
mention
filtering when you select from, say, the ProjectList combo box. Is there
a
combo box After Update event that causes the filtering to occur?
Something
needs to happen, as the act of selecting a value from a combo box will
not
by itself cause anything to happen.

Thank you Bruce for your prompt response. I tried all of your
suggestions
to
no avail. Let me try to explain the purpose.

I have a list of records with numerous columns of information displayed
in
a
subform (frmSubProjectList) that is contained within a form
(frmProjectList).
All of the columns of all of the records accept two (ProjectNumber) and
(ProjectManager) are required fields on Data Entry. Therefore, the
number
and manager fields may be blank for a while after the record is
entered.

So.... if I am using the form (frmProjectList) to display specific
records,
I would filter. If I want to filter by, lets say, location for
example, I
would select a location from a combo box and the list in the subform
would
reduce to display only those records that have my selected location in
them.
As stated above, the locations column is one of those that is required
so
all
the records have this field populated.

Having said that, I now want to filter the entire list of records to
show,
lets say, only the records that have project number 98765 in the
project
number field. I would select this number (98765) from the combo box
and
what
should happen is the same as in the location example, it should reduce
to
only those related records. Unfortunately, the project number field is
not
required on data entry, so some of the records have a blank (Null)
value,
and
some of them have a number. I think this is why I can not get the
filter
to
work. That is my intention.

I have the field ProjectNumber set to data type text in the main table
(tblProjectData), and the project number table (tblProjNumber). I
have
the
relationship set to "One-to-Many". In both tables, the object has no
set
format, no mask, no default value, and no validation, and "allow zero
length"
is set to "yes", just very straight forward.

The Project Number combo box is unbound and the name is
cboProjectNumberChoice

and the Row Source is

SELECT ProjectNumber FROM tblProjectNumber ORDER BY [ProjectNumber];

I have the same configuration for all the other combo boxes that work.

The sub form (fromSubProjectList) uses a query (qryProjects) as a
record
source.

The qryProjects has several criteria in its were clause. Here is an
example, in part:

WHERE ....
....AND
((tblLocation.Location)=IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice]))
.....

This is for the location example above. I tried the same type of
criteria
using the Is Not Null or the Nz and niether work.

Again, thank you for your prompt response. I appreciate anything
anyone
can
do to help. Let me know if you need any more specific info.

Wendy


:

To eliminate null values, add a WHERE clause:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber Is Not Null
ORDER BY [ProjectNumber]

If the lack of a project number is a 0 or a zero-length string rather
than
Null you could test accordingly:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber <> 0
ORDER BY [ProjectNumber]

SELECT ProjectNumber
FROM tblProjectNumber
WHERE Len(ProjectNumber) > 0
ORDER BY [ProjectNumber]

If I misunderstand your intention, you could instead use the Nz
function
to
replace Null with a value:

SELECT Nz(ProjectNumber,0) FROM tblProjectNumber
ORDER BY ProjectNumber

Without knowing how you are using the ProjectNumber value in the
filter
or
whether you want any records returned if PorjectNumber is null I
cannot
evaluate whether the suggestion will work, but if not it may point you
in
the right direction.

Help! I have created a form with 5 seperate combo boxes designed to
filter a
list in a subform either seperately or in combination. Three of
them
work
fine, but the other two do not. The problem is that the value could
potentially be blank in the column the combo box is set up to
recognize.

For example:

Combo box name: cboProjectNumberChoice
Row Source: SELECT ProjectNumber FROM tblProjectNumber
ORDER BY [ProjectNumber];

tblProjectNumber contains all current project number in the DB with
the
the
user to add a new one. The problem is that a record can be added to
the
DB
and the Project Number may not be assigned for weeks after.
Therefore
the
record will show up in the list, but may not have a Project Number.

I still want the user to be able to filter the list by Project
Number
and
I
can not get it to work. The filter does nothing.

All the working filters have the same type of row source in them.
For
example the location combo box has a working filter:

Combo box name: cboLocationChoice
Row Source: SELECT Location FROM tblLocation ORDER BY [Location];


The subform is generated by a record source that is a Query. The
query
has
a condition in it for the Location that looks like this:

IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])

I have tried every kind of condition I know of to get the
ProjectNumber
filter to work. Can anyone help me? I have a deadline of the end
of
this
week.

Thanks!

 

[BruceM]

Just to be clear about my earlier response, you need the After Update code
in every combo box that may be used for searching. Also, search combo boxes
should almost certainly be unbound.

Also, to answer more of your question, once the value is selected in the
combo box, (in a working combo box), the list reduces to only display the
records with that value. In the case of the combo boxes that are not
working, nothing happens at all. I still see the full list of
records....unfiltered.

Wendy

Null and zero-length strings are not at all the same thing, even though
both
have the same result when viewing the data in that you do not see
anything.
More here:
http://allenbrowne.com/casu-11.html

There is little reason to change "Allow zero length" to Yes.

How are you applying the filter? In what way does it not work? You
mention
filtering when you select from, say, the ProjectList combo box. Is there
a
combo box After Update event that causes the filtering to occur?
Something
needs to happen, as the act of selecting a value from a combo box will
not
by itself cause anything to happen.

Thank you Bruce for your prompt response. I tried all of your
suggestions
to
no avail. Let me try to explain the purpose.

I have a list of records with numerous columns of information displayed
in
a
subform (frmSubProjectList) that is contained within a form
(frmProjectList).
All of the columns of all of the records accept two (ProjectNumber) and
(ProjectManager) are required fields on Data Entry. Therefore, the
number
and manager fields may be blank for a while after the record is
entered.

So.... if I am using the form (frmProjectList) to display specific
records,
I would filter. If I want to filter by, lets say, location for
example, I
would select a location from a combo box and the list in the subform
would
reduce to display only those records that have my selected location in
them.
As stated above, the locations column is one of those that is required
so
all
the records have this field populated.

Having said that, I now want to filter the entire list of records to
show,
lets say, only the records that have project number 98765 in the
project
number field. I would select this number (98765) from the combo box
and
what
should happen is the same as in the location example, it should reduce
to
only those related records. Unfortunately, the project number field is
not
required on data entry, so some of the records have a blank (Null)
value,
and
some of them have a number. I think this is why I can not get the
filter
to
work. That is my intention.

I have the field ProjectNumber set to data type text in the main table
(tblProjectData), and the project number table (tblProjNumber). I
have
the
relationship set to "One-to-Many". In both tables, the object has no
set
format, no mask, no default value, and no validation, and "allow zero
length"
is set to "yes", just very straight forward.

The Project Number combo box is unbound and the name is
cboProjectNumberChoice

and the Row Source is

SELECT ProjectNumber FROM tblProjectNumber ORDER BY [ProjectNumber];

I have the same configuration for all the other combo boxes that work.

The sub form (fromSubProjectList) uses a query (qryProjects) as a
record
source.

The qryProjects has several criteria in its were clause. Here is an
example, in part:

WHERE ....
....AND
((tblLocation.Location)=IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice]))
.....

This is for the location example above. I tried the same type of
criteria
using the Is Not Null or the Nz and niether work.

Again, thank you for your prompt response. I appreciate anything
anyone
can
do to help. Let me know if you need any more specific info.

Wendy


:

To eliminate null values, add a WHERE clause:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber Is Not Null
ORDER BY [ProjectNumber]

If the lack of a project number is a 0 or a zero-length string rather
than
Null you could test accordingly:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber <> 0
ORDER BY [ProjectNumber]

SELECT ProjectNumber
FROM tblProjectNumber
WHERE Len(ProjectNumber) > 0
ORDER BY [ProjectNumber]

If I misunderstand your intention, you could instead use the Nz
function
to
replace Null with a value:

SELECT Nz(ProjectNumber,0) FROM tblProjectNumber
ORDER BY ProjectNumber

Without knowing how you are using the ProjectNumber value in the
filter
or
whether you want any records returned if PorjectNumber is null I
cannot
evaluate whether the suggestion will work, but if not it may point you
in
the right direction.

Help! I have created a form with 5 seperate combo boxes designed to
filter a
list in a subform either seperately or in combination. Three of
them
work
fine, but the other two do not. The problem is that the value could
potentially be blank in the column the combo box is set up to
recognize.

For example:

Combo box name: cboProjectNumberChoice
Row Source: SELECT ProjectNumber FROM tblProjectNumber
ORDER BY [ProjectNumber];

tblProjectNumber contains all current project number in the DB with
the
the
user to add a new one. The problem is that a record can be added to
the
DB
and the Project Number may not be assigned for weeks after.
Therefore
the
record will show up in the list, but may not have a Project Number.

I still want the user to be able to filter the list by Project
Number
and
I
can not get it to work. The filter does nothing.

All the working filters have the same type of row source in them.
For
example the location combo box has a working filter:

Combo box name: cboLocationChoice
Row Source: SELECT Location FROM tblLocation ORDER BY [Location];


The subform is generated by a record source that is a Query. The
query
has
a condition in it for the Location that looks like this:

IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])

I have tried every kind of condition I know of to get the
ProjectNumber
filter to work. Can anyone help me? I have a deadline of the end
of
this
week.

Thanks!

 

[ken]

The fact that some of the rows in the table have Null at the
ProjectNumber column position should not prevent you restricting the
query's result set, and hence the subform, on that column. For each
row where the value in the ProjectNumber column matches the select
number the criterion will evaluate to True, so the row will be
returned; for each row with Null at the ProjectNumber column position
it will evaluate to Null, so the row won’t be returned.

One thing I'm not clear about is whether your criteria are mutually
exclusive or not, i.e. after selecting a location, when you then
select a project number, do you want those rows returned where the
location matches the selected location *and* the project number
matches the selected project number, or do you want those rows
returned where the project number matches the selected project number
irrespective of the location?

Whichever is the case the query underlying the subform should be along
these lines:

SELECT *
FROM YourTable
WHERE (Location = [Forms]![frmProjectList]![cboLocationChoice]
OR [Forms]![frmProjectList]![cboLocationChoice] IS NULL)
AND (ProjectNumber = [Forms]![frmProjectList]![cboProjectNumber]
OR [Forms]![frmProjectList]![cboProjectNumber] IS NULL)
AND
<and so on for other combo boxes>;

The parentheses are important here as each OR operation needs to
evaluate independently of the AND operations.

If the criteria are mutually exclusive then you not only need to
requery the subform in each combo box's AfterUpdate event procedure,
but prior to this you need to set each of the other four combo boxes
to Null. Each parenthesised expression in the query for those combo
boxes other than the relevant one will then evaluate to True, so the
WHERE clause as a whole will evaluate to True only for those rows
where the value in the relevant column matches the value selected in
the combo box. Consequently only those rows will be returned. So for
cboProjectNumber's AfterUpdate event procedure the code would be:

Me.cboLocationChoice = Null
< repeat for each other combo box bar cboProjectNumber>
Me.frmSubProjectList.Requery

Note that frmSubProjectList here is the name of the subform control,
i.e. the control in the main parent form which houses the subform. It
might or might not also be the name of the subform's underlying form
object.

If the criteria are not mutually exclusive but complementary, however,
then you do not need to set the other four to Null, merely requery the
subform.

Do nothing in the controls' Change event procedures.

Ken Sheridan
Stafford, England

I'm sorry Bruce. This WOULD be important.....there is an "After Update"
Event Procedure as follows:

Private Sub cboProjectNumberChoice_AfterUpdate()
DoCmd.Requery "frmSubProjectList"
End Sub

As well as an "On Change" Event Procedure as follows :

Private Sub cboLocationChoice_Change()
DoCmd.Requery "frmSubProjectList"
End Sub

Thanks!

Wendy

Null and zero-length strings are not at all the same thing, even thoughboth
have the same result when viewing the data in that you do not see anything.
More here:
http://allenbrowne.com/casu-11.html

There is little reason to change "Allow zero length" to Yes.

How are you applying the filter? In what way does it not work? You mention
filtering when you select from, say, the ProjectList combo box. Is there a
combo box After Update event that causes the filtering to occur? Something
needs to happen, as the act of selecting a value from a combo box will not
by itself cause anything to happen.

Thank you Bruce for your prompt response. I tried all of your suggestions
to
no avail. Let me try to explain the purpose.
I have a list of records with numerous columns of information displayed in
a
subform (frmSubProjectList) that is contained within a form
(frmProjectList).
All of the columns of all of the records accept two (ProjectNumber) and
(ProjectManager) are required fields on Data Entry. Therefore, the number
and manager fields may be blank for a while after the record is entered.
So.... if I am using the form (frmProjectList) to display specific
records,
I would filter. If I want to filter by, lets say, location for example, I
would select a location from a combo box and the list in the subform would
reduce to display only those records that have my selected location in
them.
As stated above, the locations column is one of those that is required so
all
the records have this field populated.
Having said that, I now want to filter the entire list of records to show,
lets say, only the records that have project number 98765 in the project
number field. I would select this number (98765) from the combo box and
what
should happen is the same as in the location example, it should reduce to
only those related records. Unfortunately, the project number field is
not
required on data entry, so some of the records have a blank (Null) value,
and
some of them have a number. I think this is why I can not get the filter
to
work. That is my intention.
I have the field ProjectNumber set to data type text in the main table
(tblProjectData), and the project number table (tblProjNumber). I have
the
relationship set to "One-to-Many". In both tables, the object has noset
format, no mask, no default value, and no validation, and "allow zero
length"
is set to "yes", just very straight forward.
The Project Number combo box is unbound and the name is
cboProjectNumberChoice
and the Row Source is
SELECT ProjectNumber FROM tblProjectNumber ORDER BY [ProjectNumber];
I have the same configuration for all the other combo boxes that work..
The sub form (fromSubProjectList) uses a query (qryProjects) as a record
source.
The qryProjects has several criteria in its were clause. Here is an
example, in part:
WHERE ....
....AND
((tblLocation.Location)=IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice]))
.....
This is for the location example above. I tried the same type of criteria
using the Is Not Null or the Nz and niether work.
Again, thank you for your prompt response. I appreciate anything anyone
can
do to help. Let me know if you need any more specific info.
Wendy
:
To eliminate null values, add a WHERE clause:
SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber Is Not Null
ORDER BY [ProjectNumber]
If the lack of a project number is a 0 or a zero-length string rather
than
Null you could test accordingly:
SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber <> 0
ORDER BY [ProjectNumber]
SELECT ProjectNumber
FROM tblProjectNumber
WHERE Len(ProjectNumber) > 0
ORDER BY [ProjectNumber]
If I misunderstand your intention, you could instead use the Nz function
to
replace Null with a value:
SELECT Nz(ProjectNumber,0) FROM tblProjectNumber
ORDER BY ProjectNumber
Without knowing how you are using the ProjectNumber value in the filter
or
whether you want any records returned if PorjectNumber is null I cannot
evaluate whether the suggestion will work, but if not it may point you in
the right direction.
Help! I have created a form with 5 seperate combo boxes designed to
filter a
list in a subform either seperately or in combination. Three of them
work
fine, but the other two do not. The problem is that the value could
potentially be blank in the column the combo box is set up to
recognize.
For example:
Combo box name: cboProjectNumberChoice
Row Source: SELECT ProjectNumber FROM tblProjectNumber
ORDER BY [ProjectNumber];
tblProjectNumber contains all current project number in the DB with the
the
user to add a new one. The problem is that a record can be added to
the
DB
and the Project Number may not be assigned for weeks after. Therefore
the
record will show up in the list, but may not have a Project Number..
I still want the user to be able to filter the list by Project Number
and
I
can not get it to work. The filter does nothing.
All the working filters have the same type of row source in them. For
example the location combo box has a working filter:
Combo box name: cboLocationChoice
Row Source: SELECT Location FROM tblLocation ORDER BY [Location];
The subform is generated by a record source that is a Query. The query
has
a condition in it for the Location that looks like this:
IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])
I have tried every kind of condition I know of to get the ProjectNumber
filter to work. Can anyone help me? I have a deadline of the endof
this
week.
Thanks!

 

[Wendymel]

John,

Thank you! The criterion that you suggested worked! It was all I needed as
I changed nothing else.

When I did put the criteria in the query however, I had to use parenthesis
or it would really complicate matters. I wrote it like:

(([tblProjectNumber].[ProjectNumber])=[Forms]![frmProjectList]![cboProjectNumberChoice] Or [Forms]![frmProjectList]![cboProjectNumberChoice] Is Null)

Thank you again!

Wendy

The subform is generated by a record source that is a Query. The query has
a condition in it for the Location that looks like this:

IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])

I have tried every kind of condition I know of to get the ProjectNumber
filter to work. Can anyone help me? I have a deadline of the end of this
week.

Try this instead as a criterion:

=[Forms]![frmProjectList]![cboLocationChoice] OR
[Forms]![frmProjectList]![cboLocationChoice] IS NULL

 

[Wendymel]

Bruce,

Thank you for all your guidance. I ended up changing the criteria in my
query as suggested by John Vinson and it worked.

Thanks again!

Wendy

Just to be clear about my earlier response, you need the After Update code
in every combo box that may be used for searching. Also, search combo boxes
should almost certainly be unbound.

Also, to answer more of your question, once the value is selected in the
combo box, (in a working combo box), the list reduces to only display the
records with that value. In the case of the combo boxes that are not
working, nothing happens at all. I still see the full list of
records....unfiltered.

Wendy

Null and zero-length strings are not at all the same thing, even though
both
have the same result when viewing the data in that you do not see
anything.
More here:
http://allenbrowne.com/casu-11.html

There is little reason to change "Allow zero length" to Yes.

How are you applying the filter? In what way does it not work? You
mention
filtering when you select from, say, the ProjectList combo box. Is there
a
combo box After Update event that causes the filtering to occur?
Something
needs to happen, as the act of selecting a value from a combo box will
not
by itself cause anything to happen.

Thank you Bruce for your prompt response. I tried all of your
suggestions
to
no avail. Let me try to explain the purpose.

I have a list of records with numerous columns of information displayed
in
a
subform (frmSubProjectList) that is contained within a form
(frmProjectList).
All of the columns of all of the records accept two (ProjectNumber) and
(ProjectManager) are required fields on Data Entry. Therefore, the
number
and manager fields may be blank for a while after the record is
entered.

So.... if I am using the form (frmProjectList) to display specific
records,
I would filter. If I want to filter by, lets say, location for
example, I
would select a location from a combo box and the list in the subform
would
reduce to display only those records that have my selected location in
them.
As stated above, the locations column is one of those that is required
so
all
the records have this field populated.

Having said that, I now want to filter the entire list of records to
show,
lets say, only the records that have project number 98765 in the
project
number field. I would select this number (98765) from the combo box
and
what
should happen is the same as in the location example, it should reduce
to
only those related records. Unfortunately, the project number field is
not
required on data entry, so some of the records have a blank (Null)
value,
and
some of them have a number. I think this is why I can not get the
filter
to
work. That is my intention.

I have the field ProjectNumber set to data type text in the main table
(tblProjectData), and the project number table (tblProjNumber). I
have
the
relationship set to "One-to-Many". In both tables, the object has no
set
format, no mask, no default value, and no validation, and "allow zero
length"
is set to "yes", just very straight forward.

The Project Number combo box is unbound and the name is
cboProjectNumberChoice

and the Row Source is

SELECT ProjectNumber FROM tblProjectNumber ORDER BY [ProjectNumber];

I have the same configuration for all the other combo boxes that work.

The sub form (fromSubProjectList) uses a query (qryProjects) as a
record
source.

The qryProjects has several criteria in its were clause. Here is an
example, in part:

WHERE ....
....AND
((tblLocation.Location)=IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice]))
.....

This is for the location example above. I tried the same type of
criteria
using the Is Not Null or the Nz and niether work.

Again, thank you for your prompt response. I appreciate anything
anyone
can
do to help. Let me know if you need any more specific info.

Wendy


:

To eliminate null values, add a WHERE clause:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber Is Not Null
ORDER BY [ProjectNumber]

If the lack of a project number is a 0 or a zero-length string rather
than
Null you could test accordingly:

SELECT ProjectNumber
FROM tblProjectNumber
WHERE ProjectNumber <> 0
ORDER BY [ProjectNumber]

SELECT ProjectNumber
FROM tblProjectNumber
WHERE Len(ProjectNumber) > 0
ORDER BY [ProjectNumber]

If I misunderstand your intention, you could instead use the Nz
function
to
replace Null with a value:

SELECT Nz(ProjectNumber,0) FROM tblProjectNumber
ORDER BY ProjectNumber

Without knowing how you are using the ProjectNumber value in the
filter
or
whether you want any records returned if PorjectNumber is null I
cannot
evaluate whether the suggestion will work, but if not it may point you
in
the right direction.

Help! I have created a form with 5 seperate combo boxes designed to
filter a
list in a subform either seperately or in combination. Three of
them
work
fine, but the other two do not. The problem is that the value could
potentially be blank in the column the combo box is set up to
recognize.

For example:

Combo box name: cboProjectNumberChoice
Row Source: SELECT ProjectNumber FROM tblProjectNumber
ORDER BY [ProjectNumber];

tblProjectNumber contains all current project number in the DB with
the
the
user to add a new one. The problem is that a record can be added to
the
DB
and the Project Number may not be assigned for weeks after.
Therefore
the
record will show up in the list, but may not have a Project Number.

I still want the user to be able to filter the list by Project
Number
and
I
can not get it to work. The filter does nothing.

All the working filters have the same type of row source in them.
For
example the location combo box has a working filter:

Combo box name: cboLocationChoice
Row Source: SELECT Location FROM tblLocation ORDER BY [Location];


The subform is generated by a record source that is a Query. The
query
has
a condition in it for the Location that looks like this:

IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])

I have tried every kind of condition I know of to get the
ProjectNumber
filter to work. Can anyone help me? I have a deadline of the end
of
this
week.

Thanks!

 

[John W. Vinson]

John,

Thank you! The criterion that you suggested worked! It was all I needed as
I changed nothing else.

When I did put the criteria in the query however, I had to use parenthesis
or it would really complicate matters. I wrote it like:

(([tblProjectNumber].[ProjectNumber])=[Forms]![frmProjectList]![cboProjectNumberChoice] Or [Forms]![frmProjectList]![cboProjectNumberChoice] Is Null)

Thank you again!

Glad it worked for you. If you need to do this with multiple fields it's best
to work in the SQL window exclusively - if you go into the grid Access will
really scramble the query and it can become dauntingly complex.

For this reason, it's often better to use VBA code to actually poll through
the optional criteria controls on a form and construct a SQL string
dynamically.

 

[潇洒æ°æ°]

Help! I have created a form with 5 seperate combo boxes designed to
filter a
list in a subform either seperately or in combination. Three of them work
fine, but the other two do not. The problem is that the value could
potentially be blank in the column the combo box is set up to recognize.

For example:

Combo box name: cboProjectNumberChoice
Row Source: SELECT ProjectNumber FROM tblProjectNumber
ORDER BY [ProjectNumber];

tblProjectNumber contains all current project number in the DB with the
the
user to add a new one. The problem is that a record can be added to the
DB
and the Project Number may not be assigned for weeks after. Therefore the
record will show up in the list, but may not have a Project Number.

I still want the user to be able to filter the list by Project Number and
I
can not get it to work. The filter does nothing.

All the working filters have the same type of row source in them. For
example the location combo box has a working filter:

Combo box name: cboLocationChoice
Row Source: SELECT Location FROM tblLocation ORDER BY [Location];


The subform is generated by a record source that is a Query. The query
has
a condition in it for the Location that looks like this:

IIf(IsNull([Forms]![frmProjectList]![cboLocationChoice]),[Location],[Forms]![frmProjectList]![cboLocationChoice])

I have tried every kind of condition I know of to get the ProjectNumber
filter to work. Can anyone help me? I have a deadline of the end of this
week.

Thanks!