Can you sort based on what's in the field?

[Mark D. Hall]

This is an updated message. I accidentally sent the original one by mistake
before finishing it. Sorry about that.

I have a database of customers that I call and want to add a "Call
Again?" field to. I would rather use a text field instead of a yes/no, but
am having a problem with my query.

I thought I could simply use "< > "N"" in the criteria field to display all
customers that don't have an "N" in the "Call Again?" field, but no
customers at all show when I have it set that way. It works fine if I use
"Is Null," if I haven't entered a "Y" in there, but once I enter a "Y," that
customer no longer shows because the field is no longer null. Is it
possible to sort like this? Any help would be great. Thanks.

Mark

 

[James]

Hi

I believe "<>"N"" (note without the first and last quotes) should work
as your criteria but this would have to match the entire field, i.e.
nothing in the field apart from "N". You could use "Not Like "*" & "N"
& "*"" which would give you every record except those than contain a N.

James

 

[Mark D. Hall]

Hi James,

I tried what you suggested, and it too didn't work. Nothing shows up in the
query results.

Mark--
To reply directly, remove the NOSPAM

 

[John Spencer]

IF you are trying to filter records and want all those that contain a value
you would use criteria of
Is Not Null

If you want all those that have a null or something other than N
Is Not Null AND <> "N"

If your field could contain N or No or Never then
Is Not Null AND NOT Like "N*"

The "problem" here is that NULL is never equal to anything and is never not
equal to anything.

Another way to solve this problem would be to use a calculated column with
the NZ function (NZ converts Nulls to a specified value)
Field: CallAgain: NZ([Call Again?],"")
Criteria: <> "N"

That works because the column contains a zero-length string (zls). You can
compare a zls to "N" and get True or False. Using this latter technique can
be slow in large tables, since no indexes can be used and every row of the
table has the NZ function called before the comparison can be done.

 

[Mark Hall]

Basically, I'd like to show all the records all the time unless there is
an N in the field. I agree about the Null not being the best solution and
am thinking that it's better to change the field to a "DontCall" yes/no
field. That would work smoother I think, but before I do, I would like to
try what you suggest using the NZ function. It sounds fairly straight
forward, but I'm not sure where or how to do it. Where would I enter the
Field info? Does it replace the normal field data at the top? Thanks.

IF you are trying to filter records and want all those that contain a
value
you would use criteria of
Is Not Null

If you want all those that have a null or something other than N
Is Not Null AND <> "N"

If your field could contain N or No or Never then
Is Not Null AND NOT Like "N*"

The "problem" here is that NULL is never equal to anything and is never
not
equal to anything.

Another way to solve this problem would be to use a calculated column
with
the NZ function (NZ converts Nulls to a specified value)
Field: CallAgain: NZ([Call Again?],"")
Criteria: <> "N"

That works because the column contains a zero-length string (zls). You
can
compare a zls to "N" and get True or False. Using this latter technique
can
be slow in large tables, since no indexes can be used and every row of
the
table has the NZ function called before the comparison can be done.

 

[John Spencer]

You add an additional column to the query. If you are using the query grid
(View Query) you keep what you have and in add
This example assumes the field is named [Call Again?].

Field: CallAgain: NZ([Call Again?],"")
Criteria: <> "N"

By the way, you are much better off using field and table names that don't
use spaces and uses only characters that are in the alphabet or are numbers.

Basically, I'd like to show all the records all the time unless there is
an N in the field. I agree about the Null not being the best solution and
am thinking that it's better to change the field to a "DontCall" yes/no
field. That would work smoother I think, but before I do, I would like to
try what you suggest using the NZ function. It sounds fairly straight
forward, but I'm not sure where or how to do it. Where would I enter the
Field info? Does it replace the normal field data at the top? Thanks.

IF you are trying to filter records and want all those that contain a
value
you would use criteria of
Is Not Null

If you want all those that have a null or something other than N
Is Not Null AND <> "N"

If your field could contain N or No or Never then
Is Not Null AND NOT Like "N*"

The "problem" here is that NULL is never equal to anything and is never
not
equal to anything.

Another way to solve this problem would be to use a calculated column
with
the NZ function (NZ converts Nulls to a specified value)
Field: CallAgain: NZ([Call Again?],"")
Criteria: <> "N"

That works because the column contains a zero-length string (zls). You
can
compare a zls to "N" and get True or False. Using this latter technique
can
be slow in large tables, since no indexes can be used and every row of
the
table has the NZ function called before the comparison can be done.

This is an updated message. I accidentally sent the original one by
mistake before finishing it. Sorry about that.

I have a database of customers that I call and want to add a "Call
Again?" field to. I would rather use a text field instead of a yes/no,
but am having a problem with my query.

I thought I could simply use "< > "N"" in the criteria field to display
all
customers that don't have an "N" in the "Call Again?" field, but no
customers at all show when I have it set that way. It works fine if I
use
"Is Null," if I haven't entered a "Y" in there, but once I enter a "Y,"
that customer no longer shows because the field is no longer null. Is
it
possible to sort like this? Any help would be great. Thanks.

Mark

 

[Mark D. Hall]

John,

Thank you, thank you!!

I tried your suggestion, and it worked perfectly. The field name was
actually "CallAgain", so I changed that and then the name of the new
"virtual" field to "CallEmAgain," and it worked great.

Are there any resources that tell about these types of special
expressions/queries? I have a book on Access, but it only has about 6 pages
of info on expressions and doesn't really cover anything like this.

--
To reply directly, remove the NOSPAM

You add an additional column to the query. If you are using the query
grid (View Query) you keep what you have and in add
This example assumes the field is named [Call Again?].

Field: CallAgain: NZ([Call Again?],"")
Criteria: <> "N"

By the way, you are much better off using field and table names that don't
use spaces and uses only characters that are in the alphabet or are
numbers.

Basically, I'd like to show all the records all the time unless there is
an N in the field. I agree about the Null not being the best solution
and am thinking that it's better to change the field to a "DontCall"
yes/no field. That would work smoother I think, but before I do, I would
like to try what you suggest using the NZ function. It sounds fairly
straight forward, but I'm not sure where or how to do it. Where would I
enter the Field info? Does it replace the normal field data at the top?
Thanks.

IF you are trying to filter records and want all those that contain a
value
you would use criteria of
Is Not Null

If you want all those that have a null or something other than N
Is Not Null AND <> "N"

If your field could contain N or No or Never then
Is Not Null AND NOT Like "N*"

The "problem" here is that NULL is never equal to anything and is never
not
equal to anything.

Another way to solve this problem would be to use a calculated column
with
the NZ function (NZ converts Nulls to a specified value)
Field: CallAgain: NZ([Call Again?],"")
Criteria: <> "N"

That works because the column contains a zero-length string (zls). You
can
compare a zls to "N" and get True or False. Using this latter technique
can
be slow in large tables, since no indexes can be used and every row of
the
table has the NZ function called before the comparison can be done.

This is an updated message. I accidentally sent the original one by
mistake before finishing it. Sorry about that.

I have a database of customers that I call and want to add a "Call
Again?" field to. I would rather use a text field instead of a yes/no,
but am having a problem with my query.

I thought I could simply use "< > "N"" in the criteria field to display
all
customers that don't have an "N" in the "Call Again?" field, but no
customers at all show when I have it set that way. It works fine if I
use
"Is Null," if I haven't entered a "Y" in there, but once I enter a "Y,"
that customer no longer shows because the field is no longer null. Is
it
possible to sort like this? Any help would be great. Thanks.

Mark

 

[John Spencer]

NZ is a VBA function.

If you look in the VBA help, you will see lots of functions that Access can use.

John,

Thank you, thank you!!

I tried your suggestion, and it worked perfectly. The field name was
actually "CallAgain", so I changed that and then the name of the new
"virtual" field to "CallEmAgain," and it worked great.

Are there any resources that tell about these types of special
expressions/queries? I have a book on Access, but it only has about 6 pages
of info on expressions and doesn't really cover anything like this.

--
To reply directly, remove the NOSPAM

You add an additional column to the query. If you are using the query
grid (View Query) you keep what you have and in add
This example assumes the field is named [Call Again?].

Field: CallAgain: NZ([Call Again?],"")
Criteria: <> "N"

By the way, you are much better off using field and table names that don't
use spaces and uses only characters that are in the alphabet or are
numbers.

Basically, I'd like to show all the records all the time unless there is
an N in the field. I agree about the Null not being the best solution
and am thinking that it's better to change the field to a "DontCall"
yes/no field. That would work smoother I think, but before I do, I would
like to try what you suggest using the NZ function. It sounds fairly
straight forward, but I'm not sure where or how to do it. Where would I
enter the Field info? Does it replace the normal field data at the top?
Thanks.

IF you are trying to filter records and want all those that contain a
value
you would use criteria of
Is Not Null

If you want all those that have a null or something other than N
Is Not Null AND <> "N"

If your field could contain N or No or Never then
Is Not Null AND NOT Like "N*"

The "problem" here is that NULL is never equal to anything and is never
not
equal to anything.

Another way to solve this problem would be to use a calculated column
with
the NZ function (NZ converts Nulls to a specified value)
Field: CallAgain: NZ([Call Again?],"")
Criteria: <> "N"

That works because the column contains a zero-length string (zls). You
can
compare a zls to "N" and get True or False. Using this latter technique
can
be slow in large tables, since no indexes can be used and every row of
the
table has the NZ function called before the comparison can be done.

This is an updated message. I accidentally sent the original one by
mistake before finishing it. Sorry about that.

I have a database of customers that I call and want to add a "Call
Again?" field to. I would rather use a text field instead of a yes/no,
but am having a problem with my query.

I thought I could simply use "< > "N"" in the criteria field to display
all
customers that don't have an "N" in the "Call Again?" field, but no
customers at all show when I have it set that way. It works fine if I
use
"Is Null," if I haven't entered a "Y" in there, but once I enter a "Y,"
that customer no longer shows because the field is no longer null. Is
it
possible to sort like this? Any help would be great. Thanks.

Mark