A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

IIf statement in query criteria



 
 
Thread Tools Display Modes
  #1  
Old December 8th, 2009, 09:04 PM posted to microsoft.public.access.queries
Proko
external usenet poster
 
Posts: 26
Default IIf statement in query criteria

I am having a problem with the iif statement when I use it in the criteria
for a query for a combo box (Combo2) that is based on the selection of
another combo box (Combo1)

Essentially, what I am trying to do is show all choices in combo2 if there
has not been a selection in Combo1; And show a filtered selection in Combo2
if there is a selection in Combo1.

I am trying to do this using the iif statement when setting the criteria in
the Combo2 query.

iif (isnull(combo1), No Criteria is Set, Criteria is set)

I am having trouble with the "No Criteria is Set" part of the expression.
Leaving it blank, inserting Null or "" (zero length string" doesn't work.

Why am I doing this? Not only do I want to be able to make a selection in
Combo1 then a selection in Combo2 based on Combo1's selection but I want to
be able to do it the other way round. ie make a selection in Combo2 (so I
need all choices displayed) which will automatically make a selection in
Combo1.

I hope all this makes sense.

Is there a way? Or am I going about this the wrong way.

Any help would be greatly appreciated.

Thanks
  #2  
Old December 8th, 2009, 09:13 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default IIf statement in query criteria

It would help if you showed the query you are trying to build and told us a
little bit about the type of fields you are applying the criteria against.

SELECT Field1
FROM SomeTable
WHERE Field2 = Forms![NameOfForm]![NameOfComboSource]

If Field2 always has a value and is always text you can use:
SELECT Field1
FROM SomeTable
WHERE Field2 LIKE Nz(Forms![NameOfForm]![NameOfComboSource],"*")

Otherwise you can use
SELECT Field1
FROM SomeTable
WHERE Field2 = Forms![NameOfForm]![NameOfComboSource]
OR Forms![NameOfForm]![NameOfComboSource] Is Null


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

Proko wrote:
I am having a problem with the iif statement when I use it in the criteria
for a query for a combo box (Combo2) that is based on the selection of
another combo box (Combo1)

Essentially, what I am trying to do is show all choices in combo2 if there
has not been a selection in Combo1; And show a filtered selection in Combo2
if there is a selection in Combo1.

I am trying to do this using the iif statement when setting the criteria in
the Combo2 query.

iif (isnull(combo1), No Criteria is Set, Criteria is set)

I am having trouble with the "No Criteria is Set" part of the expression.
Leaving it blank, inserting Null or "" (zero length string" doesn't work.

Why am I doing this? Not only do I want to be able to make a selection in
Combo1 then a selection in Combo2 based on Combo1's selection but I want to
be able to do it the other way round. ie make a selection in Combo2 (so I
need all choices displayed) which will automatically make a selection in
Combo1.

I hope all this makes sense.

Is there a way? Or am I going about this the wrong way.

Any help would be greatly appreciated.

Thanks

  #3  
Old December 8th, 2009, 10:08 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default IIf statement in query criteria

I am trying to do this using the iif statement when setting the criteria in
the Combo2 query.
Try this --
Like iif (isnull([combo1]), "*", [combo1])

but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1.

I do not see how making a selection in Combo2 will ever control Combo1.

--
Build a little, test a little.


"Proko" wrote:

I am having a problem with the iif statement when I use it in the criteria
for a query for a combo box (Combo2) that is based on the selection of
another combo box (Combo1)

Essentially, what I am trying to do is show all choices in combo2 if there
has not been a selection in Combo1; And show a filtered selection in Combo2
if there is a selection in Combo1.

I am trying to do this using the iif statement when setting the criteria in
the Combo2 query.

iif (isnull(combo1), No Criteria is Set, Criteria is set)

I am having trouble with the "No Criteria is Set" part of the expression.
Leaving it blank, inserting Null or "" (zero length string" doesn't work.

Why am I doing this? Not only do I want to be able to make a selection in
Combo1 then a selection in Combo2 based on Combo1's selection but I want to
be able to do it the other way round. ie make a selection in Combo2 (so I
need all choices displayed) which will automatically make a selection in
Combo1.

I hope all this makes sense.

Is there a way? Or am I going about this the wrong way.

Any help would be greatly appreciated.

Thanks

  #4  
Old December 8th, 2009, 10:25 PM posted to microsoft.public.access.queries
blindman
external usenet poster
 
Posts: 5
Default IIf statement in query criteria

I have done a similiar thing a while back, but no programming expert. Think
I did it all with tables and queries. Do something along the lines of having
three tables. table 1 and table 2 for combo1 and table 3 for combo2. Table1
has static data, table 3 and 2 get populated by update queries based on what
gets dirty on the form. That way you can update from either combo. This is
not exact and doing this from memory but it did work for me , just have a
play. I remember that me.refresh came in handy also.
--
blindman


"Proko" wrote:

I am having a problem with the iif statement when I use it in the criteria
for a query for a combo box (Combo2) that is based on the selection of
another combo box (Combo1)

Essentially, what I am trying to do is show all choices in combo2 if there
has not been a selection in Combo1; And show a filtered selection in Combo2
if there is a selection in Combo1.

I am trying to do this using the iif statement when setting the criteria in
the Combo2 query.

iif (isnull(combo1), No Criteria is Set, Criteria is set)

I am having trouble with the "No Criteria is Set" part of the expression.
Leaving it blank, inserting Null or "" (zero length string" doesn't work.

Why am I doing this? Not only do I want to be able to make a selection in
Combo1 then a selection in Combo2 based on Combo1's selection but I want to
be able to do it the other way round. ie make a selection in Combo2 (so I
need all choices displayed) which will automatically make a selection in
Combo1.

I hope all this makes sense.

Is there a way? Or am I going about this the wrong way.

Any help would be greatly appreciated.

Thanks

  #5  
Old December 9th, 2009, 12:33 AM posted to microsoft.public.access.queries
Proko
external usenet poster
 
Posts: 26
Default IIf statement in query criteria


Thanks Karl,

I'll give it a try when I get to work. Makes sense though!

Proko


"KARL DEWEY" wrote:

I am trying to do this using the iif statement when setting the criteria in

the Combo2 query.
Try this --
Like iif (isnull([combo1]), "*", [combo1])

but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1.

I do not see how making a selection in Combo2 will ever control Combo1.

--
Build a little, test a little.


"Proko" wrote:

I am having a problem with the iif statement when I use it in the criteria
for a query for a combo box (Combo2) that is based on the selection of
another combo box (Combo1)

Essentially, what I am trying to do is show all choices in combo2 if there
has not been a selection in Combo1; And show a filtered selection in Combo2
if there is a selection in Combo1.

I am trying to do this using the iif statement when setting the criteria in
the Combo2 query.

iif (isnull(combo1), No Criteria is Set, Criteria is set)

I am having trouble with the "No Criteria is Set" part of the expression.
Leaving it blank, inserting Null or "" (zero length string" doesn't work.

Why am I doing this? Not only do I want to be able to make a selection in
Combo1 then a selection in Combo2 based on Combo1's selection but I want to
be able to do it the other way round. ie make a selection in Combo2 (so I
need all choices displayed) which will automatically make a selection in
Combo1.

I hope all this makes sense.

Is there a way? Or am I going about this the wrong way.

Any help would be greatly appreciated.

Thanks

  #6  
Old December 9th, 2009, 12:36 AM posted to microsoft.public.access.queries
Proko
external usenet poster
 
Posts: 26
Default IIf statement in query criteria

Thanks,

I'll give your idea some thought.

Proko



"blindman" wrote:

I have done a similiar thing a while back, but no programming expert. Think
I did it all with tables and queries. Do something along the lines of having
three tables. table 1 and table 2 for combo1 and table 3 for combo2. Table1
has static data, table 3 and 2 get populated by update queries based on what
gets dirty on the form. That way you can update from either combo. This is
not exact and doing this from memory but it did work for me , just have a
play. I remember that me.refresh came in handy also.
--
blindman


"Proko" wrote:

I am having a problem with the iif statement when I use it in the criteria
for a query for a combo box (Combo2) that is based on the selection of
another combo box (Combo1)

Essentially, what I am trying to do is show all choices in combo2 if there
has not been a selection in Combo1; And show a filtered selection in Combo2
if there is a selection in Combo1.

I am trying to do this using the iif statement when setting the criteria in
the Combo2 query.

iif (isnull(combo1), No Criteria is Set, Criteria is set)

I am having trouble with the "No Criteria is Set" part of the expression.
Leaving it blank, inserting Null or "" (zero length string" doesn't work.

Why am I doing this? Not only do I want to be able to make a selection in
Combo1 then a selection in Combo2 based on Combo1's selection but I want to
be able to do it the other way round. ie make a selection in Combo2 (so I
need all choices displayed) which will automatically make a selection in
Combo1.

I hope all this makes sense.

Is there a way? Or am I going about this the wrong way.

Any help would be greatly appreciated.

Thanks

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.