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
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).
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
> 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
> >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).
> > 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
> > 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
> > 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,
> > this be done??? Does anyone know