No success with setting query criteria for returning empty

I have imported tables from another database into Access and one of these
tables has a field that stores a value that denotes the status for each
student enrolled in a school. The values appear as "N" for students "Not
Returning" for a new school term, "I" for students withdrawing during the
term and are "Inactive" for the remainder of the term, and what appears to be
as "blanks" or "empty" for "active" students. Using a query on that table.
I'm trying to set the criteria for the Status field so that it will return
only "active" students, but having no success with trying conditions such as
Not Like "N" And Not Like "I", or "I" And "N", or Like " ", or = " ".
Each of these appear to return no data at all.
The query runs fine when setting the criteria of the Status field as either
Like "N" to return only the students "Not Returning", Like "I" to return
only students withdrawing during the term, or Like "I" or Like "N" to return
all the student who are not active. It's only when setting the criteria so
that only "active" students are returned by the query that does not seem to
work.
I'm wondering if the Status field of the imported table could have some
hidden value for the "active" students, and Access can "see" this value. The
Status field appears as being "blank" or empty for "active" students in its
original database as well. All help will be greatly appreciated.
Thanks in advance,
RC

It sounds like active Students have Null value for this Status Field. Try:

Is Null

in the criteria row for this Field column.

--
HTH
Van T. Dinh
MVP (Access)



"RC" wrote in message
...
I have imported tables from another database into Access and one of these
tables has a field that stores a value that denotes the status for each
student enrolled in a school. The values appear as "N" for students "Not
Returning" for a new school term, "I" for students withdrawing during the
term and are "Inactive" for the remainder of the term, and what appears to
be
as "blanks" or "empty" for "active" students. Using a query on that table.
I'm trying to set the criteria for the Status field so that it will return
only "active" students, but having no success with trying conditions such
as
Not Like "N" And Not Like "I", or "I" And "N", or Like " ", or = "
".
Each of these appear to return no data at all.
The query runs fine when setting the criteria of the Status field as
either
Like "N" to return only the students "Not Returning", Like "I" to return
only students withdrawing during the term, or Like "I" or Like "N" to
return
all the student who are not active. It's only when setting the criteria so
that only "active" students are returned by the query that does not seem
to
work.
I'm wondering if the Status field of the imported table could have some
hidden value for the "active" students, and Access can "see" this value.
The
Status field appears as being "blank" or empty for "active" students in
its
original database as well. All help will be greatly appreciated.
Thanks in advance,
RC

If there is nothing at all in the Status field for active students, the
value will be Null

In the Criteria row under this field in query design, enter:
Is Null

Nulls behave differently than other types of data. A null really means
'Unknown', or 'Not applicable'. Strictly, it is not the right thing to store
where the student's status is known. It would make more sense to use an
Update query to change all the nulls into another code such as "C" for
Current students. (If it's somebody else's application, that might break the
way they set up the rest of the application though.)

If you are interesting in learning about how to handle Nulls, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RC" wrote in message
...
I have imported tables from another database into Access and one of these
tables has a field that stores a value that denotes the status for each
student enrolled in a school. The values appear as "N" for students "Not
Returning" for a new school term, "I" for students withdrawing during the
term and are "Inactive" for the remainder of the term, and what appears to
be
as "blanks" or "empty" for "active" students. Using a query on that table.
I'm trying to set the criteria for the Status field so that it will return
only "active" students, but having no success with trying conditions such
as
Not Like "N" And Not Like "I", or "I" And "N", or Like " ", or = "
".
Each of these appear to return no data at all.
The query runs fine when setting the criteria of the Status field as
either
Like "N" to return only the students "Not Returning", Like "I" to return
only students withdrawing during the term, or Like "I" or Like "N" to
return
all the student who are not active. It's only when setting the criteria so
that only "active" students are returned by the query that does not seem
to
work.
I'm wondering if the Status field of the imported table could have some
hidden value for the "active" students, and Access can "see" this value.
The
Status field appears as being "blank" or empty for "active" students in
its
original database as well. All help will be greatly appreciated.
Thanks in advance,
RC

"Van T. Dinh" wrote:

It sounds like active Students have Null value for this Status Field. Try:

Is Null

in the criteria row for this Field column.

--
HTH
Van T. Dinh
MVP (Access)



"RC" wrote in message
...
I have imported tables from another database into Access and one of these
tables has a field that stores a value that denotes the status for each
student enrolled in a school. The values appear as "N" for students "Not
Returning" for a new school term, "I" for students withdrawing during the
term and are "Inactive" for the remainder of the term, and what appears to
be
as "blanks" or "empty" for "active" students. Using a query on that table.
I'm trying to set the criteria for the Status field so that it will return
only "active" students, but having no success with trying conditions such
as
Not Like "N" And Not Like "I", or "I" And "N", or Like " ", or = "
".
Each of these appear to return no data at all.
The query runs fine when setting the criteria of the Status field as
either
Like "N" to return only the students "Not Returning", Like "I" to return
only students withdrawing during the term, or Like "I" or Like "N" to
return
all the student who are not active. It's only when setting the criteria so
that only "active" students are returned by the query that does not seem
to
work.
I'm wondering if the Status field of the imported table could have some
hidden value for the "active" students, and Access can "see" this value.
The
Status field appears as being "blank" or empty for "active" students in
its
original database as well. All help will be greatly appreciated.
Thanks in advance,
RC


Thanks, that worked fine

"Allen Browne" wrote:

If there is nothing at all in the Status field for active students, the
value will be Null

In the Criteria row under this field in query design, enter:
Is Null

Nulls behave differently than other types of data. A null really means
'Unknown', or 'Not applicable'. Strictly, it is not the right thing to store
where the student's status is known. It would make more sense to use an
Update query to change all the nulls into another code such as "C" for
Current students. (If it's somebody else's application, that might break the
way they set up the rest of the application though.)

If you are interesting in learning about how to handle Nulls, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RC" wrote in message
...
I have imported tables from another database into Access and one of these
tables has a field that stores a value that denotes the status for each
student enrolled in a school. The values appear as "N" for students "Not
Returning" for a new school term, "I" for students withdrawing during the
term and are "Inactive" for the remainder of the term, and what appears to
be
as "blanks" or "empty" for "active" students. Using a query on that table.
I'm trying to set the criteria for the Status field so that it will return
only "active" students, but having no success with trying conditions such
as
Not Like "N" And Not Like "I", or "I" And "N", or Like " ", or = "
".
Each of these appear to return no data at all.
The query runs fine when setting the criteria of the Status field as
either
Like "N" to return only the students "Not Returning", Like "I" to return
only students withdrawing during the term, or Like "I" or Like "N" to
return
all the student who are not active. It's only when setting the criteria so
that only "active" students are returned by the query that does not seem
to
work.
I'm wondering if the Status field of the imported table could have some
hidden value for the "active" students, and Access can "see" this value.
The
Status field appears as being "blank" or empty for "active" students in
its
original database as well. All help will be greatly appreciated.
Thanks in advance,
RC


Thanks, that worked fine.