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

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

Post by c2FsbW9uZW » Fri, 04 Nov 2005 20:29:03


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 us< 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 <ike not >ull> 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 l<ke which will not set any criteria for the
field?

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

Many thanks
 
 
 

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

Post by Allen Brow » Fri, 04 Nov 2005 20:42:13

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.
Tips for Access users - http://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.

 
 
 

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

Post by Douglas J » Fri, 04 Nov 2005 20:47:04

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

--
Doug Steele, Microsoft Access MVP
http://www.yqcomputer.com/
(no e-mails, please!)




The
returned
not
return
is
the
null
can
that
the
 
 
 

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

Post by c2FsbW9uZW » Sat, 05 Nov 2005 00:45:06

llen, 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 have the value but
are null!!

This I why I was thinking that if there was someway to put a s null or is
not nullstatement in place of the then if the field is given a value
from the combo box, the s null or is not nullstatement 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!!!


"Allen Browne" >rote:

> Switch the query to SQL View, and change the WHERE clause to somethi>g like >> t>is:
>
> WHERE (([forms]![reports_switch_main]![combo69] >s Null)
> OR (MyTable.MyField = [forms]![reports_switch_main]![co>bo6>]))
>>
> --
> Allen Browne - Microsoft MVP. Perth, Western Au>tralia.
> Tips for Access users - http://allenbrowne.com/t>ps.html
> Reply to group, rather than allenbrowne at mvps >ot >rg.
>
> "sa<monella" wrote in>message
> news:D271E308-BD11-46DA-9005-A0355140DE3B@microsof>.>om...
> >I have a question I hope someone can help me with (a different, an> > hope
> > better, approach to a problem I have been seeking adv>c> o>)>
> >
> > I have a parameter query that gets its value from a combo box (co>b>69). >>>> The
> > following function is in the criteria grid for one of the>f>elds:
> > Like NZ([forms]![reports_switch_main]![combo>9>,">">
> >
> > When combo69 is null, I would like all records for the fiel> >o be
> > >e>urned
> > but because an "*" is used in the criteria, records with null valu>s>will >>>> not
> > be returned. I <an't use > > NZ([forms]![reports_switch_main]![com>o69],"*")> because it wi>l>also
> > >eturn
> > null values when a value is chosen from >o>bo>9>
> >
> > The answer seems to be to replace the "*" with some<hing like > not null> this way if a value is chosen in combo69, that value is >s>d in >>>> the
> > query and if not, all records will be returned for that field (>h>ther
> > or strings); (basically nullifies the criteria for that field). H>w>ver, >>>> can
> > this be done??? Does anyone know
 
 
 

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

Post by c2FsbW9uZW » Sat, 05 Nov 2005 00:46:28

oug , 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 have the value but
are null!!

This I why I was thinking that if there was someway to put a s null or is
not nullstatement in place of the then if the field is given a value
from the combo box, the s null or is not nullstatement 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!!!


"Douglas J Steele" >rote:

> = [forms]![reports_switch_main]![com>o69] OR
> [forms]![reports_switch_main]![combo69]>IS >ULL
>>
> --
> Doug Steele, Microsoft Ac>ess MVP
> http://I.Am/Do>gSteele
> (no e-mails, >lea>e!)> >
>
> "sa<monella" wrote in>message
> news:D271E308-BD11-46DA-9005-A0355140DE3B@microsof>.>om...
> > I have a question I hope someone can help me with (a different, an> > hope
> > better, approach to a problem I have been seeking adv>c> o>)>
> >
> > I have a parameter query that gets its value from a combo box (c>mbo69)> >> The
> > following function is in the criteria grid for one of the>f>elds:
> > Like NZ([forms]![reports_switch_main]![combo>9>,">">
> >
> > When combo69 is null, I would like all records for the fie>d to be
> >e>urned
> > but because an "*" is used in the criteria, records with null val>es wil> >> not
> > be returned. I <an't use > > NZ([forms]![reports_switch_main]![com>o69],"*")> because it w>ll also
> > null values when a value is chosen from >o>bo>9>
> >
> > The answer seems to be to replace the "*" with some<hing like is
> > not null> this way if a value is chosen in combo69, that value is>used i> >> the
> > query and if not, all records will be returned for that field >whether> > > or strings); (basically nullifies the criteria for that field). >owever> >> can
> > this be done??? Does anyone know how to set the criteria on the >uery so> > > it uses the value in combo69 or, if no value is chosen (null) r>t>rns a
> > st<ing like which will not set any crit>ria fo> >> the
> > >ie>d>
> >
 
 
 

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

Post by Allen Brow » Sat, 05 Nov 2005 00:56:45

id 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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"salmonella" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

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

Post by Douglas J » Sat, 05 Nov 2005 01:27:41

o, 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
http://I.Am/DougSteele
(no e-mails, please!)


"salmonella" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
the
but
is
value
if
returned)...
an
return
combo69)???
cannot
hope
(combo69).
will
or
in
However,
so
a
for


 
 
 

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

Post by c2FsbW9uZW » Sat, 05 Nov 2005 02:38:58

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!



"Douglas J Steele" wrote:

 
 
 

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

Post by Douglas J » Sat, 05 Nov 2005 02:56:32

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

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
http://I.Am/DougSteele
(no e-mails, please!)


"salmonella" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
that
is
on
null)
a
proposing)
If
True.
the
as
return
you
that
value
or
and
(through
is no
many
and i
values
also
null
used
(whether
query
returns
criteria


 
 
 

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

Post by c2FsbW9uZW » Sat, 05 Nov 2005 21:22:02

ear Allen and Doug,
I recopied and pasted Doug 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!!!!


"Douglas J Steele" wrote:

 
 
 

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

Post by c2FsbW9uZW » Sat, 05 Nov 2005 21:22:03

ear Allen and Doug,
I recopied and pasted Doug 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!!!!


"Allen Browne" wrote: