Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by salmonella, Nov 4, 2005.

  1. salmonella

    salmonella
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikh woman's forced marriage annulled in Britain (New Kerala) Breaking News Oct 22, 2008
    Sikh News Sikh woman’s marriage annulled in UK (Hindustan Times) Breaking News Oct 22, 2008
    Concept Of Sunn-Void,nullity, Essays on Sikhism Jan 16, 2008

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

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

    "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    news:D271E308-BD11-46DA-9005-A0355140DE3B@microsoft.com...
    >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
     
  4. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

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

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    news:D271E308-BD11-46DA-9005-A0355140DE3B@microsoft.com...
    > 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
    >
     
  5. salmonella

    salmonella
    Expand Collapse
    Guest

    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!!!


    "Allen Browne" wrote:

    > 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://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > news:D271E308-BD11-46DA-9005-A0355140DE3B@microsoft.com...
    > >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

    >
    >
    >
     
  6. salmonella

    salmonella
    Expand Collapse
    Guest

    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!!!


    "Douglas J Steele" wrote:

    > = [forms]![reports_switch_main]![combo69] OR
    > [forms]![reports_switch_main]![combo69] IS NULL
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > news:D271E308-BD11-46DA-9005-A0355140DE3B@microsoft.com...
    > > 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
    > >

    >
    >
    >
     
  7. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

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

    "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    news:A41E2B4E-6250-41D5-8196-C391488901E9@microsoft.com...
    > 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!!!
    >
    >
    > "Allen Browne" wrote:
    >
    >> 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]))
    >>
    >>
    >> "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    >> news:D271E308-BD11-46DA-9005-A0355140DE3B@microsoft.com...
    >> >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??
     
  8. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

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


    "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    news:07B56267-CE81-46FC-A4E9-4EA05908B1F3@microsoft.com...
    > 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!!!
    >
    >
    > "Douglas J Steele" wrote:
    >
    > > = [forms]![reports_switch_main]![combo69] OR
    > > [forms]![reports_switch_main]![combo69] IS NULL
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > > news:D271E308-BD11-46DA-9005-A0355140DE3B@microsoft.com...
    > > > 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
    > > >

    > >
    > >
    > >
     
  9. salmonella

    salmonella
    Expand Collapse
    Guest

    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:

    > 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
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > news:07B56267-CE81-46FC-A4E9-4EA05908B1F3@microsoft.com...
    > > 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!!!
    > >
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > = [forms]![reports_switch_main]![combo69] OR
    > > > [forms]![reports_switch_main]![combo69] IS NULL
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > > > news:D271E308-BD11-46DA-9005-A0355140DE3B@microsoft.com...
    > > > > 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
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
  10. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

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


    "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    news:603AA9EE-499E-4AEF-8196-39F2D335542E@microsoft.com...
    >
    > 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:
    >
    > > 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
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > > news:07B56267-CE81-46FC-A4E9-4EA05908B1F3@microsoft.com...
    > > > 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!!!
    > > >
    > > >
    > > > "Douglas J Steele" wrote:
    > > >
    > > > > = [forms]![reports_switch_main]![combo69] OR
    > > > > [forms]![reports_switch_main]![combo69] IS NULL
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no e-mails, please!)
    > > > >
    > > > >
    > > > > "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > > > > news:D271E308-BD11-46DA-9005-A0355140DE3B@microsoft.com...
    > > > > > 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
    > > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
  11. salmonella

    salmonella
    Expand Collapse
    Guest

    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!!!!


    "Douglas J Steele" wrote:

    > > 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
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > news:603AA9EE-499E-4AEF-8196-39F2D335542E@microsoft.com...
    > >
    > > 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:
    > >
    > > > 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
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > > > news:07B56267-CE81-46FC-A4E9-4EA05908B1F3@microsoft.com...
    > > > > 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!!!
    > > > >
    > > > >
    > > > > "Douglas J Steele" wrote:
    > > > >
    > > > > > = [forms]![reports_switch_main]![combo69] OR
    > > > > > [forms]![reports_switch_main]![combo69] IS NULL
    > > > > >
    > > > > > --
    > > > > > Doug Steele, Microsoft Access MVP
    > > > > > http://I.Am/DougSteele
    > > > > > (no e-mails, please!)
    > > > > >
    > > > > >
    > > > > > "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > > > > > news:D271E308-BD11-46DA-9005-A0355140DE3B@microsoft.com...
    > > > > > > 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
    > > > > > >
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
  12. salmonella

    salmonella
    Expand Collapse
    Guest

    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!!!!


    "Allen Browne" wrote:

    > 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.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > news:A41E2B4E-6250-41D5-8196-C391488901E9@microsoft.com...
    > > 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!!!
    > >
    > >
    > > "Allen Browne" wrote:
    > >
    > >> 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]))
    > >>
    > >>
    > >> "salmonella" <uwimdl@hotmail.com.dns> wrote in message
    > >> news:D271E308-BD11-46DA-9005-A0355140DE3B@microsoft.com...
    > >> >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??

    >
    >
    >
     

Share This Page