Iif Statement in query Criteria

S

SteveT

I have an imported table of address data which also has an indicator field
( INDO). INDO is either blank (Null) or set to Y.

I wish to select address data using a form checkbox so that either
1) all addresses are selected that do not have INDO set to Y
2) all addresses are selected irrespective of the INDO setting


In my query criteria I have been trying all sorts of variants along the line
of:


IIf([Forms]![Mailing Parameters]![Check112]=True,"y" Or Is Null,Is Null)

but nothing seems to work. Does anyone have any suggestions?


Many thanks

SteveT
 
J

John Spencer

WHERE ([Forms]![Mailing Parameters]![Check112] and Indo = "y")
OR [Forms]![Mailing Parameters]![Check112] = False

If you are building this in query design view

Field: [Forms]![Mailing Parameters]![Check112]
Criteria(1): True
Criteria(2): False

And under field INDO
Field: INDO
Criteria(1): "Y"
Criteria(2): <<<Leave blank>>


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
K

Krzysztof Naworyta

SteveT wrote:
| I have an imported table of address data which also has an indicator
| field ( INDO). INDO is either blank (Null) or set to Y.
|
| I wish to select address data using a form checkbox so that either
| 1) all addresses are selected that do not have INDO set to Y
| 2) all addresses are selected irrespective of the INDO setting
|
|
| In my query criteria I have been trying all sorts of variants along
| the line of:
|
|
| IIf([Forms]![Mailing Parameters]![Check112]=True,"y" Or Is Null,Is
| Null)
|
| but nothing seems to work. Does anyone have any suggestions?


IIf([Forms]![Mailing Parameters]![Check112]=True,True,[INDO] Is Null) = True
 
S

SteveT

Thank you John,

I was using query design view and using your approach have not got it
working. Many Thanks

Steve T.


John Spencer said:
WHERE ([Forms]![Mailing Parameters]![Check112] and Indo = "y")
OR [Forms]![Mailing Parameters]![Check112] = False

If you are building this in query design view

Field: [Forms]![Mailing Parameters]![Check112]
Criteria(1): True
Criteria(2): False

And under field INDO
Field: INDO
Criteria(1): "Y"
Criteria(2): <<<Leave blank>>


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have an imported table of address data which also has an indicator
field ( INDO). INDO is either blank (Null) or set to Y.

I wish to select address data using a form checkbox so that either
1) all addresses are selected that do not have INDO set to Y
2) all addresses are selected irrespective of the INDO setting


In my query criteria I have been trying all sorts of variants along the
line of:


IIf([Forms]![Mailing Parameters]![Check112]=True,"y" Or Is Null,Is Null)

but nothing seems to work. Does anyone have any suggestions?


Many thanks

SteveT
Click to expand...
Click to expand...
 
S

SteveT

Sorry but couldn't get this to work. Invalid string error or Too Complex
error message

Thanks anyway,


Steve T.
 
J

John Spencer

Did you mean you have not got it working or have "NOW" got it working?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thank you John,

I was using query design view and using your approach have not got it
working. Many Thanks

Steve T.


John Spencer said:
WHERE ([Forms]![Mailing Parameters]![Check112] and Indo = "y")
OR [Forms]![Mailing Parameters]![Check112] = False

If you are building this in query design view

Field: [Forms]![Mailing Parameters]![Check112]
Criteria(1): True
Criteria(2): False

And under field INDO
Field: INDO
Criteria(1): "Y"
Criteria(2): <<<Leave blank>>


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have an imported table of address data which also has an indicator
field ( INDO). INDO is either blank (Null) or set to Y.

I wish to select address data using a form checkbox so that either
1) all addresses are selected that do not have INDO set to Y
2) all addresses are selected irrespective of the INDO setting


In my query criteria I have been trying all sorts of variants along the
line of:


IIf([Forms]![Mailing Parameters]![Check112]=True,"y" Or Is Null,Is Null)

but nothing seems to work. Does anyone have any suggestions?


Many thanks

SteveT
Click to expand...
Click to expand...
Click to expand...
 
S

SteveT

Sorry John,

Yes, It is working thanks.

Steve T.
John Spencer said:
Did you mean you have not got it working or have "NOW" got it working?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thank you John,

I was using query design view and using your approach have not got it
working. Many Thanks

Steve T.


John Spencer said:
WHERE ([Forms]![Mailing Parameters]![Check112] and Indo = "y")
OR [Forms]![Mailing Parameters]![Check112] = False

If you are building this in query design view

Field: [Forms]![Mailing Parameters]![Check112]
Criteria(1): True
Criteria(2): False

And under field INDO
Field: INDO
Criteria(1): "Y"
Criteria(2): <<<Leave blank>>


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

SteveT wrote:
I have an imported table of address data which also has an indicator
field ( INDO). INDO is either blank (Null) or set to Y.

I wish to select address data using a form checkbox so that either
1) all addresses are selected that do not have INDO set to Y
2) all addresses are selected irrespective of the INDO setting


In my query criteria I have been trying all sorts of variants along the
line of:


IIf([Forms]![Mailing Parameters]![Check112]=True,"y" Or Is Null,Is
Null)

but nothing seems to work. Does anyone have any suggestions?


Many thanks

SteveT
Click to expand...
Click to expand...
Click to expand...
 
K

Krzysztof Naworyta

Where do you try to write it?
In query design (QBE)?
Try to write it in SQL view, in WHERE block, or replace commas with ";"

--
KN

Juzer SteveT <nospam@nospam> napisa³
| Sorry but couldn't get this to work. Invalid string error or Too
| Complex error message
|
| Thanks anyway,
|
|
| Steve T.
|

||| I have an imported table of address data which also has an indicator
||| field ( INDO). INDO is either blank (Null) or set to Y.
|||
||| I wish to select address data using a form checkbox so that either
||| 1) all addresses are selected that do not have INDO set to Y
||| 2) all addresses are selected irrespective of the INDO setting
|||
|||
||| In my query criteria I have been trying all sorts of variants along
||| the line of:
|||
|||
||| IIf([Forms]![Mailing Parameters]![Check112]=True,"y" Or Is Null,Is
||| Null)
|||
||| but nothing seems to work. Does anyone have any suggestions?
||
||
|| IIf([Forms]![Mailing Parameters]![Check112]=True,True,[INDO] Is Null) =
|| True
 
J

John Spencer

Ok, just wanted to make sure that we didn't need to follow up to help you.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIf statement in query criteria 5
no criteria 3
IIF statement in query 7
nested if in criteria 5
IIF statement in query criteria, help! 3
Sum(IIF statement 2
parameter with iif statement in query criteria 10
If Statement with "*" in Query 2

Top