Query filter problem!

[Carma via AccessMonster.com]

Ok I'm trying to create a query which will be the basis for a report. Now
the query results will be determined by selections made by the user on a form
called frmLists. The form will have up to five separate list boxes, but
we'll work with two for this discussion:

List 1 List 2
null null
A 1
B 2
C 3
D 4



I want the user to be able to select items form either or both list boxes and
then the query will select the correct records based upon the selected. I
thought about using separate check boxes under each list for the user to
check if they want to use 'that' list as part of the filter, but maybe there
is a better way. For List 1 I had Checkbox1 and for List 2 I had Checkbox2.
The query uses data from one table only called tblPositions. So in the query
I set up the criteria/filter for the appropriate field, say on List 2 as iif(
[Checkbox2] =-1,[List 2]) So I thought that if the user selected check box 2
it would filter by the selected list item in List 2 and that if they didn't
select check box 2 then it wouldn't filter by anything. But there are two
problems.

1) If check box isn't selected the query doesn't show any results implying to
me at least that it is filtering by a value that doesn't match anything in
the records. So I thought that I basically need the Else part of the logic
stmt above to either have no filters or somehow list all possible data
inclusions as filters but I didn't know how to do this. maybe there is a
better way too.

2) Second problem is even when checkbox2 is selected but the user selects
'empty' or null item on the list then the query comes back with no results
again even though I know the table has many records with an empty value for
this field.

Please help me and let me know if I wasn't clear in my description of the
problem.

Thanks for your help!

 

[Michel Walsh]

in the SQL view, you should have something look like:


.....
WHERE iif( FORMS!formName!checkbox2 = -1 AND (NOT FORMS!formName!combobox2
IS NULL), field2 = FORMS!formName!combobox2, true)




I assumed you use combo box, not list box.


Hoping it may help,
Vanderghast, Access MVP

 

[Carma via AccessMonster.com]

Thanks Michel for your response!

I actually have used List boxes but I could certainly use Combo Boxes. it
wouldn't make a large difference to the users.

But how do I deal with situation where user doesn't want to use a particular
list selection as a filter? Like the Else part of you first condition?

Also Null is a valid value to be used as a filter, but when the 'blank' item
is selected from list or combo box the query doesn't filter records which
have null value in that field.

Basically if they click the Check box I want to use the item selected in
filter(this part I can do basically just like you indicated below), but if
they don't click or unclick check box how can I tell query not to filter that
particular field?

Any additional help would be appreciated.

Thanks!

in the SQL view, you should have something look like:

....
WHERE iif( FORMS!formName!checkbox2 = -1 AND (NOT FORMS!formName!combobox2
IS NULL), field2 = FORMS!formName!combobox2, true)

I assumed you use combo box, not list box.

Hoping it may help,
Vanderghast, Access MVP

Ok I'm trying to create a query which will be the basis for a report. Now
the query results will be determined by selections made by the user on a

[quoted text clipped - 47 lines]

Thanks for your help!

 

[Carma via AccessMonster.com]

My SQL logic although I know its flawed is...

WHERE (((tblPositionData.[Positon Exclusion])=IIf([Forms]![frmPosition]!
[CheckExcl]=-1,[Forms]![frmPosition]![ListPosExcl], (tblPositionData.[Positon
Exclusion]) Is Null Or (tblPositionData.[Positon Exclusion]) Is Not Null)));

My 'Else' condition(trying to make the filter to give me all records which
are either null or not null-essentially the whole data set) doesn't work but
it demonstrates what I'm trying to do....

(tblPositionData.[Positon Exclusion]) Is Null Or (tblPositionData.[Positon
Exclusion]) Is Not Null)));

T

Thanks Michel for your response!

I actually have used List boxes but I could certainly use Combo Boxes. it
wouldn't make a large difference to the users.

But how do I deal with situation where user doesn't want to use a particular
list selection as a filter? Like the Else part of you first condition?

Also Null is a valid value to be used as a filter, but when the 'blank' item
is selected from list or combo box the query doesn't filter records which
have null value in that field.

Basically if they click the Check box I want to use the item selected in
filter(this part I can do basically just like you indicated below), but if
they don't click or unclick check box how can I tell query not to filter that
particular field?

Any additional help would be appreciated.

Thanks!

in the SQL view, you should have something look like:

[quoted text clipped - 12 lines]

 

[Michel Walsh]

iif( FORMS!frmPosition!CheckExcl = -1 ,
[Positon Exclusion] = FORMS![frmPosition]![ListPosExcl],
true )


solves most of the problem. If the check box checkExcl is NOT checked, the
iif returns true, which has the effect to not consider any criteria. If the
check box1 is checked, then the iff returns the result of the second
argument, which *is* the criteria, WITH the field name and the value to be
checked against. It HAS TO BE a combo box, since a LIST BOX does not really
has a 'value' .



There is still the problem with the NULL from the combo box, that is why the
first part of the iif has to handle it:



iif( FORMS!frmPosition!CheckExcl = -1 AND (NOT
FORMS!frmPosition!ListPosExcl IS NULL ) ,
...,
true )


Here again, if the combo box is null (nothing), the first argument evaluates
to false, so the iif returns TRUE, as if there was no criteria.





Vanderghast, Access MVP

My SQL logic although I know its flawed is...

WHERE (((tblPositionData.[Positon Exclusion])=IIf([Forms]![frmPosition]!
[CheckExcl]=-1,[Forms]![frmPosition]![ListPosExcl],
(tblPositionData.[Positon
Exclusion]) Is Null Or (tblPositionData.[Positon Exclusion]) Is Not
Null)));

My 'Else' condition(trying to make the filter to give me all records which
are either null or not null-essentially the whole data set) doesn't work
but
it demonstrates what I'm trying to do....

(tblPositionData.[Positon Exclusion]) Is Null Or (tblPositionData.[Positon
Exclusion]) Is Not Null)));

T

Thanks Michel for your response!

I actually have used List boxes but I could certainly use Combo Boxes. it
wouldn't make a large difference to the users.

But how do I deal with situation where user doesn't want to use a
particular
list selection as a filter? Like the Else part of you first condition?

Also Null is a valid value to be used as a filter, but when the 'blank'
item
is selected from list or combo box the query doesn't filter records which
have null value in that field.

Basically if they click the Check box I want to use the item selected in
filter(this part I can do basically just like you indicated below), but if
they don't click or unclick check box how can I tell query not to filter
that
particular field?

Any additional help would be appreciated.

Thanks!

in the SQL view, you should have something look like:

[quoted text clipped - 12 lines]

Thanks for your help!

 

[Carma via AccessMonster.com]

Thank you for your explanations. I will apply this right away.

iif( FORMS!frmPosition!CheckExcl = -1 ,
[Positon Exclusion] = FORMS![frmPosition]![ListPosExcl],
true )

solves most of the problem. If the check box checkExcl is NOT checked, the
iif returns true, which has the effect to not consider any criteria. If the
check box1 is checked, then the iff returns the result of the second
argument, which *is* the criteria, WITH the field name and the value to be
checked against. It HAS TO BE a combo box, since a LIST BOX does not really
has a 'value' .

There is still the problem with the NULL from the combo box, that is why the
first part of the iif has to handle it:

iif( FORMS!frmPosition!CheckExcl = -1 AND (NOT
FORMS!frmPosition!ListPosExcl IS NULL ) ,
...,
true )

Here again, if the combo box is null (nothing), the first argument evaluates
to false, so the iif returns TRUE, as if there was no criteria.

Vanderghast, Access MVP

My SQL logic although I know its flawed is...

[quoted text clipped - 43 lines]

 

[Carma via AccessMonster.com]

Here is what I tested... I created a combo box and selected an actual value
"n" and clicked the tick box. Here is my SQL:
WHERE (((tblPositionData.[Positon Exclusion])=IIf([Forms]![frmPosition]!
[CheckExcl]=-1,[Positon Exclusion]=[Forms]![frmPosition]![ComboExcl],True)));

When i run query and don't get any results. If I unclick the check box I get
error message that I need to simplyfy my expression.

I also tried this next expression but with the same results

WHERE (((tblPositionData.[Positon Exclusion])=IIf([Forms]![frmPosition]!
[CheckExcl]=-1,[tblPositionData]![Positon Exclusion]=[Forms]![frmPosition]!
[ComboExcl],True)));

Thank you for your explanations. I will apply this right away.

iif( FORMS!frmPosition!CheckExcl = -1 ,
[Positon Exclusion] = FORMS![frmPosition]![ListPosExcl],

[quoted text clipped - 25 lines]

 

[Michel Walsh]

You used

WHERE fieldName = iif ( ..., value, ... )

You have to use

WHERE iif( ..., fieldName=value, ... )



Vanderghast, Access MVP

Here is what I tested... I created a combo box and selected an actual
value
"n" and clicked the tick box. Here is my SQL:
WHERE (((tblPositionData.[Positon Exclusion])=IIf([Forms]![frmPosition]!
[CheckExcl]=-1,[Positon
Exclusion]=[Forms]![frmPosition]![ComboExcl],True)));

When i run query and don't get any results. If I unclick the check box I
get
error message that I need to simplyfy my expression.

I also tried this next expression but with the same results

WHERE (((tblPositionData.[Positon Exclusion])=IIf([Forms]![frmPosition]!
[CheckExcl]=-1,[tblPositionData]![Positon
Exclusion]=[Forms]![frmPosition]!
[ComboExcl],True)));

Thank you for your explanations. I will apply this right away.

iif( FORMS!frmPosition!CheckExcl = -1 ,
[Positon Exclusion] = FORMS![frmPosition]![ListPosExcl],

[quoted text clipped - 25 lines]

Thanks for your help!

 

[Carma via AccessMonster.com]

Hi Michel,

Thank you sooo much for being soo patient and helpful. Your correction below
worked perfectly. After applying and verifying that it worked I proceeded to
add the: 'And (Not FORMS!frmPosition!ListPosExcl Is Null) to my SQL', as you
suggested to produce the following SQL. Now I'm left with just the one small
problem which is that 'null' is a valid selection in my combo box and I do
want to be to filter records which have null in the field [Position Exclusion]
.. But when I unclick 'CheckExcl' and select the 'null' value from my
combobox my query doesn't produce any results. Did I make another mistake in
my SQL?

WHERE (((IIf(Forms!frmPosition!CheckExcl=-1 And (Not FORMS!frmPosition!
ListPosExcl Is Null),tblPositionData![Positon Exclusion]=Forms!frmPosition!
ComboExcl,True))<>False));

Thanks a million!

You used

WHERE fieldName = iif ( ..., value, ... )

You have to use

WHERE iif( ..., fieldName=value, ... )

Vanderghast, Access MVP

Here is what I tested... I created a combo box and selected an actual
value

[quoted text clipped - 23 lines]

 

[Carma via AccessMonster.com]

Oops I made a mistake below, i meant...'when I CLICK 'CheckExcl' and select...
'

Hi Michel,

Thank you sooo much for being soo patient and helpful. Your correction below
worked perfectly. After applying and verifying that it worked I proceeded to
add the: 'And (Not FORMS!frmPosition!ListPosExcl Is Null) to my SQL', as you
suggested to produce the following SQL. Now I'm left with just the one small
problem which is that 'null' is a valid selection in my combo box and I do
want to be to filter records which have null in the field [Position Exclusion]
. But when I unclick 'CheckExcl' and select the 'null' value from my
combobox my query doesn't produce any results. Did I make another mistake in
my SQL?

WHERE (((IIf(Forms!frmPosition!CheckExcl=-1 And (Not FORMS!frmPosition!
ListPosExcl Is Null),tblPositionData![Positon Exclusion]=Forms!frmPosition!
ComboExcl,True))<>False));

Thanks a million!

You used

[quoted text clipped - 11 lines]

 

[Carma via AccessMonster.com]

I'm just going to repost a full corrected ad so that there's as little
confusion as possible and also because I found a minor mistake in my SQL but
one that didn't improve my outcome.

Hi Michel,

Thank you sooo much for being soo patient and helpful. Your correction below
worked perfectly. After applying and verifying that it worked I proceeded to
add the: 'And (Not FORMS!frmPosition!ListPosExcl Is Null) to my SQL', as you
suggested to produce the following SQL:

WHERE (((IIf(Forms!frmPosition!CheckExcl=-1 And (Not FORMS!frmPosition!
ComboExcl Is Null),tblPositionData![Positon Exclusion]=Forms!frmPosition!
ComboExcl,True))<>False));

Now I'm left with just the one small problem which is that 'null' is a valid
selection in my combo box and I do want to be to filter records which have
null in the field [Position Exclusion]. But when I click 'CheckExcl' and
select the 'null' value from my combobox my query doesn't produce any results.
Did I make another mistake in my SQL?


Thanks a million!

Oops I made a mistake below, i meant...'when I CLICK 'CheckExcl' and select...
'

Hi Michel,

[quoted text clipped - 19 lines]

 

[Michel Walsh]

WHERE iif( FORMS!frmPosition!ComboExcl IS NULL, tblPositionData![Positon
Exclusion] IS NULL,
IIf(Forms!frmPosition!CheckExcl=-1 And (Not
FORMS!frmPosition!
ComboExcl Is Null),tblPositionData![Positon
Exclusion]=Forms!frmPosition!
ComboExcl,True) )


.... if I got my ( ) ok.



Vanderghast, Access MVP

I'm just going to repost a full corrected ad so that there's as little
confusion as possible and also because I found a minor mistake in my SQL
but
one that didn't improve my outcome.

Hi Michel,

Thank you sooo much for being soo patient and helpful. Your correction
below
worked perfectly. After applying and verifying that it worked I proceeded
to
add the: 'And (Not FORMS!frmPosition!ListPosExcl Is Null) to my SQL', as
you
suggested to produce the following SQL:

WHERE (((IIf(Forms!frmPosition!CheckExcl=-1 And (Not FORMS!frmPosition!
ComboExcl Is Null),tblPositionData![Positon Exclusion]=Forms!frmPosition!
ComboExcl,True))<>False));

Now I'm left with just the one small problem which is that 'null' is a
valid
selection in my combo box and I do want to be to filter records which have
null in the field [Position Exclusion]. But when I click 'CheckExcl' and
select the 'null' value from my combobox my query doesn't produce any
results.
Did I make another mistake in my SQL?


Thanks a million!

Oops I made a mistake below, i meant...'when I CLICK 'CheckExcl' and
select...
'

Hi Michel,

[quoted text clipped - 19 lines]

Thanks for your help!

 

[Carma via AccessMonster.com]

Hi Michel,

I used what you indicated with one small adjustment so that the query filter
is null only when the combo box is null and the check box is selected. here
is my sql with the adjustment

WHERE (((IIf([Forms]![frmPosition]![CheckExcl]=-1 And ([FORMS]![frmPosition]!
[ComboExcl] Is Null),[tblPositionData]![Positon Exclusion] Is Null,IIf([Forms]
![frmPosition]![CheckExcl]=-1 And (Not [FORMS]![frmPosition]![ComboExcl] Is
Null),[tblPositionData]![Positon Exclusion]=[Forms]![frmPosition]![ComboExcl],
True)))<>False));

The only thing I don't understand because I've never used it before is the
logic of the "true" and "<>False" in the sql.

Well thank you soo much for your help. Your generousity in taking the time
to help me is much appreciated. I hope that I can develop a fraction of your
skills one day.

WHERE iif( FORMS!frmPosition!ComboExcl IS NULL, tblPositionData![Positon
Exclusion] IS NULL,
IIf(Forms!frmPosition!CheckExcl=-1 And (Not
FORMS!frmPosition!
ComboExcl Is Null),tblPositionData![Positon
Exclusion]=Forms!frmPosition!
ComboExcl,True) )

... if I got my ( ) ok.

Vanderghast, Access MVP

I'm just going to repost a full corrected ad so that there's as little
confusion as possible and also because I found a minor mistake in my SQL

[quoted text clipped - 34 lines]

 

[Michel Walsh]

While false is the numerical value 0, true is any other not-null value. It
is -1 if it comes from the system, from a comparison, but +1 is also
considered "true". So, for a comparison to true, <> 0, not false, is
preferable to = -1 (since, true can be +1, as well).

Jet does not require that you make a test, since Jet recognizes a Boolean
value. But for compatibility (with MS SQL Server as example), you may
explicitly add a test, such as Access does, adding <> false




Vanderghast, Access MVP

Hi Michel,

I used what you indicated with one small adjustment so that the query
filter
is null only when the combo box is null and the check box is selected.
here
is my sql with the adjustment

WHERE (((IIf([Forms]![frmPosition]![CheckExcl]=-1 And
([FORMS]![frmPosition]!
[ComboExcl] Is Null),[tblPositionData]![Positon Exclusion] Is
Null,IIf([Forms]
![frmPosition]![CheckExcl]=-1 And (Not [FORMS]![frmPosition]![ComboExcl]
Is
Null),[tblPositionData]![Positon
Exclusion]=[Forms]![frmPosition]![ComboExcl],
True)))<>False));

The only thing I don't understand because I've never used it before is the
logic of the "true" and "<>False" in the sql.

Well thank you soo much for your help. Your generousity in taking the
time
to help me is much appreciated. I hope that I can develop a fraction of
your
skills one day.

WHERE iif( FORMS!frmPosition!ComboExcl IS NULL, tblPositionData![Positon
Exclusion] IS NULL,
IIf(Forms!frmPosition!CheckExcl=-1 And (Not
FORMS!frmPosition!
ComboExcl Is Null),tblPositionData![Positon
Exclusion]=Forms!frmPosition!
ComboExcl,True) )

... if I got my ( ) ok.

Vanderghast, Access MVP

I'm just going to repost a full corrected ad so that there's as little
confusion as possible and also because I found a minor mistake in my SQL

[quoted text clipped - 34 lines]

Thanks for your help!