SQL- can a "is null or is not null" be used?

[salmonella]

I have a question I hope someone can help me with (a different, and i hope
better, approach to a problem I have been seeking advice on).

I have a parameter query that gets its value from a combo box (combo69). The
following function is in the criteria grid for one of the fields:
Like NZ([forms]![reports_switch_main]![combo69],"*")

When combo69 is null, I would like all records for the field to be returned
but because an “*†is used in the criteria, records with null values will not
be returned. I can’t use <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> because it will also return
null values when a value is chosen from combo69.

The answer seems to be to replace the “*†with something like<is null or is
not null> this way if a value is chosen in combo69, that value is used in the
query and if not, all records will be returned for that field (whether null
or strings); (basically nullifies the criteria for that field). However, can
this be done??? Does anyone know how to set the criteria on the query so that
it uses the value in combo69 or, if no value is chosen (null) returns a
string like <is null or is not null> which will not set any criteria for the
field?

This is really killing me!....any ideas??

Many thanks

 

[Allen Browne]

Switch the query to SQL View, and change the WHERE clause to something like
this:

WHERE (([forms]![reports_switch_main]![combo69] Is Null)
OR (MyTable.MyField = [forms]![reports_switch_main]![combo69]))

 

[Douglas J Steele]

= [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

 

[salmonella]

Allen, thanks for the help!


What you said is close but it will not work. It is, I believe, the same as
putting for criteria in the design grid <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> in other words, return the
record if the field is null or has the value in combo69

The problem here is that if there is a record with a null value and you
specified some value as the parameter in combo69, then records for that
parameter will be returned ALONG with records that don’t have the value but
are null!!

This I why I was thinking that if there was someway to put a “is null or is
not null†statement in place of the “*â€, then if the field is given a value
from the combo box, the “is null or is not null†statement is ignored and if
not, then all records (including those with null fields will be returned)…..
Perfect!!!.

So………..can you think of anyway to get rid of the â€*†and return (through an
nz, iif, etc. function, etc. ) something instead of the “*†that will return
all records, including those where the field is null only when there is no
value in comb69 (in actuality I have lots of combo boxes besides combo69)???

This seems like a basic need for many combo boxes (parameters) across many
tables yet it seems no one knows how to do it and without it I cannot
restrict the data returned in any meaningfull way!!! For example, I cannot
pull all records of certain types of bacteria, with certain laboratory
characteristics, obtained from certain samples, from certain areas, at
certain times of the year, etc.

PLEASE any help would be appreciated!!!

Switch the query to SQL View, and change the WHERE clause to something like
this:

WHERE (([forms]![reports_switch_main]![combo69] Is Null)
OR (MyTable.MyField = [forms]![reports_switch_main]![combo69]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a question I hope someone can help me with (a different, and i hope
better, approach to a problem I have been seeking advice on).

I have a parameter query that gets its value from a combo box (combo69).
The
following function is in the criteria grid for one of the fields:
Like NZ([forms]![reports_switch_main]![combo69],"*")

When combo69 is null, I would like all records for the field to be
returned
but because an "*" is used in the criteria, records with null values will
not
be returned. I can't use <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> because it will also
return
null values when a value is chosen from combo69.

The answer seems to be to replace the "*" with something like<is null or
is
not null> this way if a value is chosen in combo69, that value is used in
the
query and if not, all records will be returned for that field (whether
null
or strings); (basically nullifies the criteria for that field). However,
can
this be done??? Does anyone know how to set the criteria on the query so
that
it uses the value in combo69 or, if no value is chosen (null) returns a
string like <is null or is not null> which will not set any criteria for
the
field?

This is really killing me!....any ideas??

Many thanks

 

[salmonella]

Doug , thanks for the help!


What you said is close but it did not work. It is, I believe, the same as
putting for criteria in the design grid <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> in other words, return the
record if the field is null or has the value in combo69

The problem here is that if there is a record with a null value and you
specified some value as the parameter in combo69, then records for that
parameter will be returned ALONG with records that don’t have the value but
are null!!

This I why I was thinking that if there was someway to put a “is null or is
not null†statement in place of the “*â€, then if the field is given a value
from the combo box, the “is null or is not null†statement is ignored and if
not, then all records (including those with null fields will be returned)…..
Perfect!!!.

So………..can you think of anyway to get rid of the â€*†and return (through an
nz, iif, etc. function, etc. ) something instead of the “*†that will return
all records, including those where the field is null only when there is no
value in comb69 (in actuality I have lots of combo boxes besides combo69)???

This seems like a basic need for many combo boxes (parameters) across many
tables yet it seems no one knows how to do it and without it I cannot
restrict the data returned in any meaningfull way!!! For example, I cannot
pull all records of certain types of bacteria, with certain laboratory
characteristics, obtained from certain samples, from certain areas, at
certain times of the year, etc.

PLEASE any help would be appreciated!!!

= [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a question I hope someone can help me with (a different, and i hope
better, approach to a problem I have been seeking advice on).

I have a parameter query that gets its value from a combo box (combo69). The
following function is in the criteria grid for one of the fields:
Like NZ([forms]![reports_switch_main]![combo69],"*")

When combo69 is null, I would like all records for the field to be returned
but because an "*" is used in the criteria, records with null values will not
be returned. I can't use <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> because it will also return
null values when a value is chosen from combo69.

The answer seems to be to replace the "*" with something like<is null or is
not null> this way if a value is chosen in combo69, that value is used in the
query and if not, all records will be returned for that field (whether null
or strings); (basically nullifies the criteria for that field). However, can
this be done??? Does anyone know how to set the criteria on the query so that
it uses the value in combo69 or, if no value is chosen (null) returns a
string like <is null or is not null> which will not set any criteria for the
field?

This is really killing me!....any ideas??

Many thanks

 

[Allen Browne]

Did you try it?

The expression:
(([forms]![reports_switch_main]![combo69] Is Null)
returns True if the combo is null.
Therefore the SQL statement performs *no* filtering when the combo is null.

If the combo is not null, the first part of the expression is False.
The OR will still return a result if the 2nd part is True.
Therefore, if the combo is not null, the query returns only records that
match, not records where the field is null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for the help!


What you said is close but it will not work. It is, I believe, the same as
putting for criteria in the design grid <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> in other words, return
the
record if the field is null or has the value in combo69

The problem here is that if there is a record with a null value and you
specified some value as the parameter in combo69, then records for that
parameter will be returned ALONG with records that don't have the value
but
are null!!

This I why I was thinking that if there was someway to put a "is null or
is
not null" statement in place of the "*", then if the field is given a
value
from the combo box, the "is null or is not null" statement is ignored and
if
not, then all records (including those with null fields will be
returned)...
Perfect!!!.

So.....can you think of anyway to get rid of the "*" and return (through
an
nz, iif, etc. function, etc. ) something instead of the "*" that will
return
all records, including those where the field is null only when there is no
value in comb69 (in actuality I have lots of combo boxes besides
combo69)???

This seems like a basic need for many combo boxes (parameters) across many
tables yet it seems no one knows how to do it and without it I cannot
restrict the data returned in any meaningfull way!!! For example, I
cannot
pull all records of certain types of bacteria, with certain laboratory
characteristics, obtained from certain samples, from certain areas, at
certain times of the year, etc.

PLEASE any help would be appreciated!!!

Switch the query to SQL View, and change the WHERE clause to something
like
this:

WHERE (([forms]![reports_switch_main]![combo69] Is Null)
OR (MyTable.MyField = [forms]![reports_switch_main]![combo69]))

I have a question I hope someone can help me with (a different, and i
hope
better, approach to a problem I have been seeking advice on).

I have a parameter query that gets its value from a combo box
(combo69).
The
following function is in the criteria grid for one of the fields:
Like NZ([forms]![reports_switch_main]![combo69],"*")

When combo69 is null, I would like all records for the field to be
returned
but because an "*" is used in the criteria, records with null values
will
not
be returned. I can't use <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> because it will also
return
null values when a value is chosen from combo69.

The answer seems to be to replace the "*" with something like<is null
or
is
not null> this way if a value is chosen in combo69, that value is used
in
the
query and if not, all records will be returned for that field (whether
null
or strings); (basically nullifies the criteria for that field).
However,
can
this be done??? Does anyone know how to set the criteria on the query
so
that
it uses the value in combo69 or, if no value is chosen (null) returns a
string like <is null or is not null> which will not set any criteria
for
the
field?

This is really killing me!....any ideas??

 

[Douglas J Steele]

No, it's not the same (and, FWIW, it's the same solution Allen's proposing)

It's looking whether the combo box is Null, not the field in the table. If
the combo box is Null, then the second part of the expression will be True.
Since we're using OR to join them, it doesn't matter what the field in the
table is: it's going to be selected.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug , thanks for the help!


What you said is close but it did not work. It is, I believe, the same as
putting for criteria in the design grid <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> in other words, return the
record if the field is null or has the value in combo69

The problem here is that if there is a record with a null value and you
specified some value as the parameter in combo69, then records for that
parameter will be returned ALONG with records that don't have the value but
are null!!

This I why I was thinking that if there was someway to put a "is null or is
not null" statement in place of the "*", then if the field is given a value
from the combo box, the "is null or is not null" statement is ignored and if
not, then all records (including those with null fields will be returned)...
Perfect!!!.

So.....can you think of anyway to get rid of the "*" and return (through an
nz, iif, etc. function, etc. ) something instead of the "*" that will return
all records, including those where the field is null only when there is no
value in comb69 (in actuality I have lots of combo boxes besides combo69)???

This seems like a basic need for many combo boxes (parameters) across many
tables yet it seems no one knows how to do it and without it I cannot
restrict the data returned in any meaningfull way!!! For example, I cannot
pull all records of certain types of bacteria, with certain laboratory
characteristics, obtained from certain samples, from certain areas, at
certain times of the year, etc.

PLEASE any help would be appreciated!!!

= [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a question I hope someone can help me with (a different, and i hope
better, approach to a problem I have been seeking advice on).

I have a parameter query that gets its value from a combo box

(combo69).
The

following function is in the criteria grid for one of the fields:
Like NZ([forms]![reports_switch_main]![combo69],"*")

When combo69 is null, I would like all records for the field to be returned
but because an "*" is used in the criteria, records with null values

will
not

be returned. I can't use <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> because it will also return
null values when a value is chosen from combo69.

The answer seems to be to replace the "*" with something like<is null

or
is

not null> this way if a value is chosen in combo69, that value is used

in
the

query and if not, all records will be returned for that field (whether null
or strings); (basically nullifies the criteria for that field).

However,
can

this be done??? Does anyone know how to set the criteria on the query

so
that

it uses the value in combo69 or, if no value is chosen (null) returns a
string like <is null or is not null> which will not set any criteria

for
the

field?

This is really killing me!....any ideas??

Many thanks

 

[salmonella]

Hi Doug,

Yes, it looked the same, that is why I did not try his. When I cut and
pasted yours into the criteria grid and ran it, it returned all records that
were null AND those that had the value I put in the combo69- which is the
problem. anytime there is a record with a null value and I use a (..... OR is
null) expression, then if a record is null for the field and I choose a
parameter value, I get records based both on the parameter and those based on
a null value- not good!.

It seems that the solution is simple, if it exists. Simply get rid of the
"*" (which does not return nulls) and replace it with an expression or
something that returns all records null or not (i.e. is null or is not null)

Maybe I am doing something wrong. Are you saying that if I use
= [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

just as this is in a criteria grid of a query and I have some records with a
null value for this field, that if I give combo69 a value, such as a
bacteria's name, that it will return only records with the bacteria's name
and not those records that have a null value for the field?

If so, PLEASE tell me what I am doing wrong with the expression!!!

Many, Many thanks!

No, it's not the same (and, FWIW, it's the same solution Allen's proposing)

It's looking whether the combo box is Null, not the field in the table. If
the combo box is Null, then the second part of the expression will be True.
Since we're using OR to join them, it doesn't matter what the field in the
table is: it's going to be selected.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug , thanks for the help!


What you said is close but it did not work. It is, I believe, the same as
putting for criteria in the design grid <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> in other words, return the
record if the field is null or has the value in combo69

The problem here is that if there is a record with a null value and you
specified some value as the parameter in combo69, then records for that
parameter will be returned ALONG with records that don't have the value but
are null!!

This I why I was thinking that if there was someway to put a "is null or is
not null" statement in place of the "*", then if the field is given a value
from the combo box, the "is null or is not null" statement is ignored and if
not, then all records (including those with null fields will be returned)...
Perfect!!!.

So.....can you think of anyway to get rid of the "*" and return (through an
nz, iif, etc. function, etc. ) something instead of the "*" that will return
all records, including those where the field is null only when there is no
value in comb69 (in actuality I have lots of combo boxes besides combo69)???

This seems like a basic need for many combo boxes (parameters) across many
tables yet it seems no one knows how to do it and without it I cannot
restrict the data returned in any meaningfull way!!! For example, I cannot
pull all records of certain types of bacteria, with certain laboratory
characteristics, obtained from certain samples, from certain areas, at
certain times of the year, etc.

PLEASE any help would be appreciated!!!

= [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a question I hope someone can help me with (a different, and i hope
better, approach to a problem I have been seeking advice on).

I have a parameter query that gets its value from a combo box (combo69).
The
following function is in the criteria grid for one of the fields:
Like NZ([forms]![reports_switch_main]![combo69],"*")

When combo69 is null, I would like all records for the field to be
returned
but because an "*" is used in the criteria, records with null values will
not
be returned. I can't use <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> because it will also
return
null values when a value is chosen from combo69.

The answer seems to be to replace the "*" with something like<is null or
is
not null> this way if a value is chosen in combo69, that value is used in
the
query and if not, all records will be returned for that field (whether
null
or strings); (basically nullifies the criteria for that field). However,
can
this be done??? Does anyone know how to set the criteria on the query so
that
it uses the value in combo69 or, if no value is chosen (null) returns a
string like <is null or is not null> which will not set any criteria for
the
field?

This is really killing me!....any ideas??

Many thanks

 

[Douglas J Steele]

Maybe I am doing something wrong. Are you saying that if I use

= [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

just as this is in a criteria grid of a query and I have some records with a
null value for this field, that if I give combo69 a value, such as a
bacteria's name, that it will return only records with the bacteria's name
and not those records that have a null value for the field?

Yes.

If it's not working, then you probably are best going into the SQL of the
query (View | SQL View from the menu) and checking that your SQL has

WHERE MyField = [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

Do you have anything else as part of your WHERE clause? If so, post the
entire query here.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

Yes, it looked the same, that is why I did not try his. When I cut and
pasted yours into the criteria grid and ran it, it returned all records that
were null AND those that had the value I put in the combo69- which is the
problem. anytime there is a record with a null value and I use a (..... OR is
null) expression, then if a record is null for the field and I choose a
parameter value, I get records based both on the parameter and those based on
a null value- not good!.

It seems that the solution is simple, if it exists. Simply get rid of the
"*" (which does not return nulls) and replace it with an expression or
something that returns all records null or not (i.e. is null or is not null)

Maybe I am doing something wrong. Are you saying that if I use
= [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

just as this is in a criteria grid of a query and I have some records with a
null value for this field, that if I give combo69 a value, such as a
bacteria's name, that it will return only records with the bacteria's name
and not those records that have a null value for the field?

If so, PLEASE tell me what I am doing wrong with the expression!!!

Many, Many thanks!

No, it's not the same (and, FWIW, it's the same solution Allen's proposing)

It's looking whether the combo box is Null, not the field in the table. If
the combo box is Null, then the second part of the expression will be True.
Since we're using OR to join them, it doesn't matter what the field in the
table is: it's going to be selected.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug , thanks for the help!


What you said is close but it did not work. It is, I believe, the same as
putting for criteria in the design grid <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> in other words,

return
the

record if the field is null or has the value in combo69

The problem here is that if there is a record with a null value and you
specified some value as the parameter in combo69, then records for that
parameter will be returned ALONG with records that don't have the

value
but

are null!!

This I why I was thinking that if there was someway to put a "is null

or
is

not null" statement in place of the "*", then if the field is given a value
from the combo box, the "is null or is not null" statement is ignored

and
if

not, then all records (including those with null fields will be returned)...
Perfect!!!.

So.....can you think of anyway to get rid of the "*" and return

(through
an

nz, iif, etc. function, etc. ) something instead of the "*" that will return
all records, including those where the field is null only when there is no
value in comb69 (in actuality I have lots of combo boxes besides combo69)???

This seems like a basic need for many combo boxes (parameters) across many
tables yet it seems no one knows how to do it and without it I cannot
restrict the data returned in any meaningfull way!!! For example, I cannot
pull all records of certain types of bacteria, with certain laboratory
characteristics, obtained from certain samples, from certain areas, at
certain times of the year, etc.

PLEASE any help would be appreciated!!!


:

= [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a question I hope someone can help me with (a different,

and i
hope

better, approach to a problem I have been seeking advice on).

I have a parameter query that gets its value from a combo box (combo69).
The
following function is in the criteria grid for one of the fields:
Like NZ([forms]![reports_switch_main]![combo69],"*")

When combo69 is null, I would like all records for the field to be
returned
but because an "*" is used in the criteria, records with null

values
will

not
be returned. I can't use <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> because it will also
return
null values when a value is chosen from combo69.

The answer seems to be to replace the "*" with something like<is

null
or

is
not null> this way if a value is chosen in combo69, that value is

used
in

the
query and if not, all records will be returned for that field (whether
null
or strings); (basically nullifies the criteria for that field). However,
can
this be done??? Does anyone know how to set the criteria on the

query
so

that
it uses the value in combo69 or, if no value is chosen (null)

returns
a

string like <is null or is not null> which will not set any

criteria
for

the
field?

This is really killing me!....any ideas??

Many thanks

 

[salmonella]

Dear Allen and Doug,
I recopied and pasted Doug’s suggestion and this time it worked this time (I
had left in an < that was not suppose to be there). I have been testing it in
a number of ways and it seems to be working fine.

Both your suggestions appear to be on the money and, although for you it is
probably a very simple problem, for me it was a BIG help.

Thanks again for your time!!!!

Maybe I am doing something wrong. Are you saying that if I use
= [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

just as this is in a criteria grid of a query and I have some records with a
null value for this field, that if I give combo69 a value, such as a
bacteria's name, that it will return only records with the bacteria's name
and not those records that have a null value for the field?

Yes.

If it's not working, then you probably are best going into the SQL of the
query (View | SQL View from the menu) and checking that your SQL has

WHERE MyField = [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

Do you have anything else as part of your WHERE clause? If so, post the
entire query here.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

Yes, it looked the same, that is why I did not try his. When I cut and
pasted yours into the criteria grid and ran it, it returned all records that
were null AND those that had the value I put in the combo69- which is the
problem. anytime there is a record with a null value and I use a (..... OR is
null) expression, then if a record is null for the field and I choose a
parameter value, I get records based both on the parameter and those based on
a null value- not good!.

It seems that the solution is simple, if it exists. Simply get rid of the
"*" (which does not return nulls) and replace it with an expression or
something that returns all records null or not (i.e. is null or is not null)

Maybe I am doing something wrong. Are you saying that if I use
= [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

just as this is in a criteria grid of a query and I have some records with a
null value for this field, that if I give combo69 a value, such as a
bacteria's name, that it will return only records with the bacteria's name
and not those records that have a null value for the field?

If so, PLEASE tell me what I am doing wrong with the expression!!!

Many, Many thanks!

No, it's not the same (and, FWIW, it's the same solution Allen's proposing)

It's looking whether the combo box is Null, not the field in the table. If
the combo box is Null, then the second part of the expression will be True.
Since we're using OR to join them, it doesn't matter what the field in the
table is: it's going to be selected.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug , thanks for the help!


What you said is close but it did not work. It is, I believe, the same as
putting for criteria in the design grid <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> in other words, return
the
record if the field is null or has the value in combo69

The problem here is that if there is a record with a null value and you
specified some value as the parameter in combo69, then records for that
parameter will be returned ALONG with records that don't have the value
but
are null!!

This I why I was thinking that if there was someway to put a "is null or
is
not null" statement in place of the "*", then if the field is given a
value
from the combo box, the "is null or is not null" statement is ignored and
if
not, then all records (including those with null fields will be
returned)...
Perfect!!!.

So.....can you think of anyway to get rid of the "*" and return (through
an
nz, iif, etc. function, etc. ) something instead of the "*" that will
return
all records, including those where the field is null only when there is no
value in comb69 (in actuality I have lots of combo boxes besides
combo69)???

This seems like a basic need for many combo boxes (parameters) across many
tables yet it seems no one knows how to do it and without it I cannot
restrict the data returned in any meaningfull way!!! For example, I
cannot
pull all records of certain types of bacteria, with certain laboratory
characteristics, obtained from certain samples, from certain areas, at
certain times of the year, etc.

PLEASE any help would be appreciated!!!


:

= [forms]![reports_switch_main]![combo69] OR
[forms]![reports_switch_main]![combo69] IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a question I hope someone can help me with (a different, and i
hope
better, approach to a problem I have been seeking advice on).

I have a parameter query that gets its value from a combo box
(combo69).
The
following function is in the criteria grid for one of the fields:
Like NZ([forms]![reports_switch_main]![combo69],"*")

When combo69 is null, I would like all records for the field to be
returned
but because an "*" is used in the criteria, records with null values
will
not
be returned. I can't use <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> because it will also
return
null values when a value is chosen from combo69.

The answer seems to be to replace the "*" with something like<is null
or
is
not null> this way if a value is chosen in combo69, that value is used
in
the
query and if not, all records will be returned for that field (whether
null
or strings); (basically nullifies the criteria for that field).
However,
can
this be done??? Does anyone know how to set the criteria on the query
so
that
it uses the value in combo69 or, if no value is chosen (null) returns
a
string like <is null or is not null> which will not set any criteria
for
the
field?

This is really killing me!....any ideas??

Many thanks

 

[salmonella]

Dear Allen and Doug,
I recopied and pasted Doug’s suggestion and this time it worked (I had left
in an < that was not suppose to be there). I have been testing it in a number
of ways and it seems to be working fine.

Both your suggestions appear to be on the money and, although for you it is
probably a very simple problem, for me it was a BIG help.

Thanks again for your time!!!!

Did you try it?

The expression:
(([forms]![reports_switch_main]![combo69] Is Null)
returns True if the combo is null.
Therefore the SQL statement performs *no* filtering when the combo is null.

If the combo is not null, the first part of the expression is False.
The OR will still return a result if the 2nd part is True.
Therefore, if the combo is not null, the query returns only records that
match, not records where the field is null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for the help!


What you said is close but it will not work. It is, I believe, the same as
putting for criteria in the design grid <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> in other words, return
the
record if the field is null or has the value in combo69

The problem here is that if there is a record with a null value and you
specified some value as the parameter in combo69, then records for that
parameter will be returned ALONG with records that don't have the value
but
are null!!

This I why I was thinking that if there was someway to put a "is null or
is
not null" statement in place of the "*", then if the field is given a
value
from the combo box, the "is null or is not null" statement is ignored and
if
not, then all records (including those with null fields will be
returned)...
Perfect!!!.

So.....can you think of anyway to get rid of the "*" and return (through
an
nz, iif, etc. function, etc. ) something instead of the "*" that will
return
all records, including those where the field is null only when there is no
value in comb69 (in actuality I have lots of combo boxes besides
combo69)???

This seems like a basic need for many combo boxes (parameters) across many
tables yet it seems no one knows how to do it and without it I cannot
restrict the data returned in any meaningfull way!!! For example, I
cannot
pull all records of certain types of bacteria, with certain laboratory
characteristics, obtained from certain samples, from certain areas, at
certain times of the year, etc.

PLEASE any help would be appreciated!!!

Switch the query to SQL View, and change the WHERE clause to something
like
this:

WHERE (([forms]![reports_switch_main]![combo69] Is Null)
OR (MyTable.MyField = [forms]![reports_switch_main]![combo69]))


I have a question I hope someone can help me with (a different, and i
hope
better, approach to a problem I have been seeking advice on).

I have a parameter query that gets its value from a combo box
(combo69).
The
following function is in the criteria grid for one of the fields:
Like NZ([forms]![reports_switch_main]![combo69],"*")

When combo69 is null, I would like all records for the field to be
returned
but because an "*" is used in the criteria, records with null values
will
not
be returned. I can't use <is null or Like
NZ([forms]![reports_switch_main]![combo69],"*")> because it will also
return
null values when a value is chosen from combo69.

The answer seems to be to replace the "*" with something like<is null
or
is
not null> this way if a value is chosen in combo69, that value is used
in
the
query and if not, all records will be returned for that field (whether
null
or strings); (basically nullifies the criteria for that field).
However,
can
this be done??? Does anyone know how to set the criteria on the query
so
that
it uses the value in combo69 or, if no value is chosen (null) returns a
string like <is null or is not null> which will not set any criteria
for
the
field?

This is really killing me!....any ideas??