Difficult Criteria question

[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

 

[Allen Browne]

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.

 

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

There was another part of this that maybe you could help with. The reason
I'm setting it up the way I am is because when I use filters on pivot tables
it seems to screw up the table. The only way to limit the data seems to be
to do it at the query level before it goes to the pivot table. That is why
I'm doing it the way I am below. If any of the users had a clue how to use
pivottables I'd be ok with sending all the data and letting them manipulate
it, but that is not a viable option.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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