Welcome to SPN

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

Sign Up Now!

Difficult Criteria question

Discussion in 'Information Technology' started by Nathan, Jul 28, 2006.

  1. Nathan

    Nathan
    Expand Collapse
    Guest

    I want to enter criteria based on a form. I have 2 check boxes.
    If the first one is checked, I want "3230" to be the criteria --> "Like 3230"
    If 2nd one is checked I want everything but 3230. --> "Not Like 3230"
    If none are checked, return everything. --> "Like *"

    This worked fine when I only had to deal with "Like 3230" and "Like *"
    I had this in criteria --> Like IIf([Forms]![Main Screen]![chk3230],3230,"*")

    So now I think I need something like
    iif([Forms]![Main Screen]![chkNot3230],"Not","") Like IIf([Forms]![Plant FTC
    Main Screen]![chkRootCause] or [Forms]![Main Screen]![chkNot3230],3230,"*")

    so I end up with "Not Like 3230" when chknot3230 is marked. but I don't get
    any records for the previous attempt. I have verified that records will be
    returned if I just type "Not Like 3230".


    Any ideas?

    Thanks

    Nathan
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Hi Nathan

    The NOT is not a value, but an operator. I doubt you can get the query to
    include the operator like that.

    Additionally, Like "*" does not return all reccords: it misses those that
    are null.

    It might be easier to leave the criteria out of the query, and instead build
    the Filter string to apply to your form. That's a much more flexible
    process, and will allow you to solve both the issues above.

    For details, see:
    Search form - Handle many optional criteria
    at:
    http://allenbrowne.com/ser-62.html
    There's a sample database you can download that will let you see how it
    works.

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

    "Nathan" <Nathan@discussions.microsoft.com> wrote in message
    news:14365468-B60B-4D03-87AB-F6CEE25E538C@microsoft.com...
    >I want to enter criteria based on a form. I have 2 check boxes.
    > If the first one is checked, I want "3230" to be the criteria --> "Like
    > 3230"
    > If 2nd one is checked I want everything but 3230. --> "Not Like 3230"
    > If none are checked, return everything. --> "Like *"
    >
    > This worked fine when I only had to deal with "Like 3230" and "Like *"
    > I had this in criteria --> Like IIf([Forms]![Main
    > Screen]![chk3230],3230,"*")
    >
    > So now I think I need something like
    > iif([Forms]![Main Screen]![chkNot3230],"Not","") Like IIf([Forms]![Plant
    > FTC
    > Main Screen]![chkRootCause] or [Forms]![Main
    > Screen]![chkNot3230],3230,"*")
    >
    > so I end up with "Not Like 3230" when chknot3230 is marked. but I don't
    > get
    > any records for the previous attempt. I have verified that records will
    > be
    > returned if I just type "Not Like 3230".
    >
    >
    > Any ideas?
    >
    > Thanks
    >
    > Nathan
     
  4. Nathan

    Nathan
    Expand Collapse
    Guest

    Normally I would agree, but this is just part of a larger system for getting
    down to specific records. Plus, I never have a null value in this field,
    specifically for the reason you stated.

    This is going to be a bummer if this isn't going to work. I've got another
    path I can go down, but I was trying to avoid that.

    Thanks for you help, I didn't understand that about the "Not" part of this
    whole thing.

    "Allen Browne" wrote:

    > Hi Nathan
    >
    > The NOT is not a value, but an operator. I doubt you can get the query to
    > include the operator like that.
    >
    > Additionally, Like "*" does not return all reccords: it misses those that
    > are null.
    >
    > It might be easier to leave the criteria out of the query, and instead build
    > the Filter string to apply to your form. That's a much more flexible
    > process, and will allow you to solve both the issues above.
    >
    > For details, see:
    > Search form - Handle many optional criteria
    > at:
    > http://allenbrowne.com/ser-62.html
    > There's a sample database you can download that will let you see how it
    > works.
    >
    > --
    > 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.
    >
    > "Nathan" <Nathan@discussions.microsoft.com> wrote in message
    > news:14365468-B60B-4D03-87AB-F6CEE25E538C@microsoft.com...
    > >I want to enter criteria based on a form. I have 2 check boxes.
    > > If the first one is checked, I want "3230" to be the criteria --> "Like
    > > 3230"
    > > If 2nd one is checked I want everything but 3230. --> "Not Like 3230"
    > > If none are checked, return everything. --> "Like *"
    > >
    > > This worked fine when I only had to deal with "Like 3230" and "Like *"
    > > I had this in criteria --> Like IIf([Forms]![Main
    > > Screen]![chk3230],3230,"*")
    > >
    > > So now I think I need something like
    > > iif([Forms]![Main Screen]![chkNot3230],"Not","") Like IIf([Forms]![Plant
    > > FTC
    > > Main Screen]![chkRootCause] or [Forms]![Main
    > > Screen]![chkNot3230],3230,"*")
    > >
    > > so I end up with "Not Like 3230" when chknot3230 is marked. but I don't
    > > get
    > > any records for the previous attempt. I have verified that records will
    > > be
    > > returned if I just type "Not Like 3230".
    > >
    > >
    > > Any ideas?
    > >
    > > Thanks
    > >
    > > Nathan

    >
    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    If you are determined to do it that way, you could switch the query to SQL
    View and type some convoluted expressions directly into the WHERE clause, so
    that the different parts of the nested IIf() expression are executed
    depending on what boxes are checked on your form.

    But there are other issues to to sort out here as well. Like is a string
    operator, but the fact that your examples have no quotes around the values
    suggests this is a numeric field. You are therefore going to have to sort
    out the data type issues as well. I think you are doing it the hard way.

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

    "Nathan" <Nathan@discussions.microsoft.com> wrote in message
    news:66BC1B21-8BEE-41E7-A4EC-F0BE47BB8795@microsoft.com...
    > Normally I would agree, but this is just part of a larger system for
    > getting
    > down to specific records. Plus, I never have a null value in this field,
    > specifically for the reason you stated.
    >
    > This is going to be a bummer if this isn't going to work. I've got
    > another
    > path I can go down, but I was trying to avoid that.
    >
    > Thanks for you help, I didn't understand that about the "Not" part of this
    > whole thing.
    >
    > "Allen Browne" wrote:
    >
    >> Hi Nathan
    >>
    >> The NOT is not a value, but an operator. I doubt you can get the query to
    >> include the operator like that.
    >>
    >> Additionally, Like "*" does not return all reccords: it misses those that
    >> are null.
    >>
    >> It might be easier to leave the criteria out of the query, and instead
    >> build
    >> the Filter string to apply to your form. That's a much more flexible
    >> process, and will allow you to solve both the issues above.
    >>
    >> For details, see:
    >> Search form - Handle many optional criteria
    >> at:
    >> http://allenbrowne.com/ser-62.html
    >> There's a sample database you can download that will let you see how it
    >> works.
    >>
    >> --
    >> 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.
    >>
    >> "Nathan" <Nathan@discussions.microsoft.com> wrote in message
    >> news:14365468-B60B-4D03-87AB-F6CEE25E538C@microsoft.com...
    >> >I want to enter criteria based on a form. I have 2 check boxes.
    >> > If the first one is checked, I want "3230" to be the criteria -->
    >> > "Like
    >> > 3230"
    >> > If 2nd one is checked I want everything but 3230. --> "Not Like 3230"
    >> > If none are checked, return everything. --> "Like *"
    >> >
    >> > This worked fine when I only had to deal with "Like 3230" and "Like *"
    >> > I had this in criteria --> Like IIf([Forms]![Main
    >> > Screen]![chk3230],3230,"*")
    >> >
    >> > So now I think I need something like
    >> > iif([Forms]![Main Screen]![chkNot3230],"Not","") Like
    >> > IIf([Forms]![Plant
    >> > FTC
    >> > Main Screen]![chkRootCause] or [Forms]![Main
    >> > Screen]![chkNot3230],3230,"*")
    >> >
    >> > so I end up with "Not Like 3230" when chknot3230 is marked. but I don't
    >> > get
    >> > any records for the previous attempt. I have verified that records
    >> > will
    >> > be
    >> > returned if I just type "Not Like 3230".
     

Share This Page