Welcome to SPN

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

Sign Up Now!
  1. Guest ji, please consider donating today!
      Become a Supporter    ::   Make a Contribution   
    Monthly Recurring Target: $300 :: Achieved: $95

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".
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page