By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,390 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

iif Function in query criteria

P: n/a
Built a form based on a select query with a combo box (3 choices) in
the form header which will supply a value to the query. In the
query's criteria I added the following expression - [Forms]![Form_1]!
[Combo_1] and this works like a charm. However one of my users wanted
to see all of the values before choosing one of the three
selections.

I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])

My original 3 choices still work but not my "All". I have tried "Like
"*"", "Is Not Null" and "*" but nothing seems to work.

Any suggestions?

Jun 2 '07 #1
Share this Question
Share on Google+
18 Replies


P: n/a
WU10 wrote:
Built a form based on a select query with a combo box (3 choices) in
the form header which will supply a value to the query. In the
query's criteria I added the following expression - [Forms]![Form_1]!
[Combo_1] and this works like a charm. However one of my users wanted
to see all of the values before choosing one of the three
selections.

I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])

My original 3 choices still work but not my "All". I have tried "Like
"*"", "Is Not Null" and "*" but nothing seems to work.

Any suggestions?
[Forms]![Form_1]![Combo_1]

....and then under that on the next criteria row...

[Forms]![Form_1]![Combo_1] = "All"

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jun 2 '07 #2

P: n/a
On Jun 2, 1:01 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
WU10 wrote:
Built a form based on a select query with a combo box (3 choices) in
the form header which will supply a value to the query. In the
query's criteria I added the following expression - [Forms]![Form_1]!
[Combo_1] and this works like a charm. However one of my users wanted
to see all of the values before choosing one of the three
selections.
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])
My original 3 choices still work but not my "All". I have tried "Like
"*"", "Is Not Null" and "*" but nothing seems to work.
Any suggestions?

[Forms]![Form_1]![Combo_1]

...and then under that on the next criteria row...

[Forms]![Form_1]![Combo_1] = "All"

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -
Rick,

Should I forget the IIF statement and use the next criteria as you
suggest?

Jun 2 '07 #3

P: n/a
WU10 wrote:
On Jun 2, 1:01 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
WU10 wrote:
Built a form based on a select query with a combo box (3 choices)
in the form header which will supply a value to the query. In the
query's criteria I added the following expression -
[Forms]![Form_1]! [Combo_1] and this works like a charm. However
one of my users wanted to see all of the values before choosing
one of the three
selections.
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])
My original 3 choices still work but not my "All". I have tried
"Like "*"", "Is Not Null" and "*" but nothing seems to work.
Any suggestions?
[Forms]![Form_1]![Combo_1]

...and then under that on the next criteria row...

[Forms]![Form_1]![Combo_1] = "All"

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -

Rick,

Should I forget the IIF statement and use the next criteria as you
suggest?
Yes.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jun 2 '07 #4

P: n/a
On Jun 2, 1:20 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
WU10 wrote:
On Jun 2, 1:01 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
WU10 wrote:
Built a form based on a select query with a combo box (3 choices)
in the form header which will supply a value to the query. In the
query's criteria I added the following expression -
[Forms]![Form_1]! [Combo_1] and this works like a charm. However
one of my users wanted to see all of the values before choosing
one of the three
selections.
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])
My original 3 choices still work but not my "All". I have tried
"Like "*"", "Is Not Null" and "*" but nothing seems to work.
Any suggestions?
[Forms]![Form_1]![Combo_1]
...and then under that on the next criteria row...
[Forms]![Form_1]![Combo_1] = "All"
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -
- Show quoted text -
Rick,
Should I forget the IIF statement and use the next criteria as you
suggest?

Yes.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -
This does not seem to work. But thanks for the reply.

Jun 2 '07 #5

P: n/a
WU10 wrote:
This does not seem to work. But thanks for the reply.
Post the SQL of your query because it has to work if you do it properly.

Think about what it does. It says "Return records where a field value matches
the value of a control on a form, OR where the control on the form is equal to
'All'".

So...when the control's value is "All" the second part of the criteria will be
true for 100% of your records and you will therefore get all of them returned.

If you already have the part working when the control is set to something
besides "All" then there really is no reason for the second part to not work.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Jun 2 '07 #6

P: n/a
Put the following in the vriteria:
[Forms]![Form_1]![Combo_1] Or ( [Forms]![Form_1]![Combo_1] Is Null)

If you do not make a selection in the combobox, you will see all the
records.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com



"WU10" <cl**********@hotmail.comwrote in message
news:11**********************@q69g2000hsb.googlegr oups.com...
Built a form based on a select query with a combo box (3 choices) in
the form header which will supply a value to the query. In the
query's criteria I added the following expression - [Forms]![Form_1]!
[Combo_1] and this works like a charm. However one of my users wanted
to see all of the values before choosing one of the three
selections.

I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])

My original 3 choices still work but not my "All". I have tried "Like
"*"", "Is Not Null" and "*" but nothing seems to work.

Any suggestions?

Jun 2 '07 #7

P: n/a
"Steve" <so***@private.emailaddresswrote in
news:Rr*******************@newsread2.news.pas.eart hlink.net:
Put the following in the vriteria:
[Forms]![Form_1]![Combo_1] Or ( [Forms]![Form_1]![Combo_1] Is
Null)

If you do not make a selection in the combobox, you will see
all the records.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And
Word Applications
re******@pcdatasheet.com
Wrong answer again Steve. The O.P. doesn't want to handle null, he
wants to handle "ALL" as in
[Forms]![Form_1]![Combo_1] Or [Forms]![Form_1]![Combo_1]= "ALL"

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jun 2 '07 #8

P: n/a
Your MRP apps must really be something! They probably have a lot of
inaccurate records where you store calculated values in tables and other
messed up things because you do not know how Access works as you demonstarte
here.

Read my lips ---
<< If you do not make a selection in the combobox, you will see all the
records. >>
When no selection is made in the combobox, the combobox will have a value of
Null and the sewcond part of this criteria will return all records.

Try it you arrogant $$Arse; it works!! Quit criticizing things you don't
understand!!!!
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
"Steve" <so***@private.emailaddresswrote in
news:Rr*******************@newsread2.news.pas.eart hlink.net:
>Put the following in the vriteria:
[Forms]![Form_1]![Combo_1] Or ( [Forms]![Form_1]![Combo_1] Is
Null)

If you do not make a selection in the combobox, you will see
all the records.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And
Word Applications
re******@pcdatasheet.com
Wrong answer again Steve. The O.P. doesn't want to handle null, he
wants to handle "ALL" as in
[Forms]![Form_1]![Combo_1] Or [Forms]![Form_1]![Combo_1]= "ALL"

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jun 2 '07 #9

P: n/a
On Sat, 02 Jun 2007 04:32:55 -0700, WU10 <cl**********@hotmail.comwrote:
>Built a form based on a select query with a combo box (3 choices) in
the form header which will supply a value to the query. In the
query's criteria I added the following expression - [Forms]![Form_1]!
[Combo_1] and this works like a charm. However one of my users wanted
to see all of the values before choosing one of the three
selections.

I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])

My original 3 choices still work but not my "All". I have tried "Like
"*"", "Is Not Null" and "*" but nothing seems to work.

Any suggestions?
In the properties for the combo box, set List Rows to 3.
When the drop down arrow in the combo box is clicked, the window will expand
downward to show the three values.

Just a wizard prodder
Chuck
--

Jun 2 '07 #10

P: n/a
"Steve" <so***@private.emailaddresswrote in
news:81******************@newsread1.news.pas.earth link.net:
Your MRP apps must really be something! They probably have a
lot of inaccurate records where you store calculated values in
tables and other messed up things because you do not know how
Access works as you demonstarte here.
I know when and how to normalize a database, and when to store a
denormalized value such as putting the UnitPrice in a
Transactions table, instead of relating it from the Items table,
>
Read my lips ---
<< If you do not make a selection in the combobox, you will
see all the records. >>
When no selection is made in the combobox, the combobox will
have a value of Null and the sewcond part of this criteria
will return all records.
With your code, maybe it works. But it's not what was wanted, an
"ALL" row in the combobox is part of the OP's specification. And
it will not work if there is a dsefault value set for the
combobox.
Try it you arrogant $$Arse; it works!! Quit criticizing things
you don't understand!!!!
I'm not arrogant. I'm pedantic.
I will thank you for having dropped the advertizing.
>
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
>"Steve" <so***@private.emailaddresswrote in
news:Rr*******************@newsread2.news.pas.ear thlink.net:
>>Put the following in the vriteria:
[Forms]![Form_1]![Combo_1] Or ( [Forms]![Form_1]![Combo_1]
Is Null)

If you do not make a selection in the combobox, you will see
all the records.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel
And Word Applications
re******@pcdatasheet.com
Wrong answer again Steve. The O.P. doesn't want to handle
null, he wants to handle "ALL" as in
[Forms]![Form_1]![Combo_1] Or [Forms]![Form_1]![Combo_1]=
"ALL"
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jun 3 '07 #11

P: n/a

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
"Steve" <so***@private.emailaddresswrote in
news:81******************@newsread1.news.pas.earth link.net:
>Your MRP apps must really be something! They probably have a
lot of inaccurate records where you store calculated values in
tables and other messed up things because you do not know how
Access works as you demonstarte here.
>I know when and how to normalize a database, and when to store a
denormalized value >>
You clearly demonstrated previously that you do not!! An OP had Length,
Width and Height fields in a table and you advised that it is okay to also
have a description field that has the value of concatenating the Length,
Width and Height fields. Most newbies know that that is wrong!!

such as putting the UnitPrice in a
Transactions table, instead of relating it from the Items table,
>>
Read my lips ---
<< If you do not make a selection in the combobox, you will
see all the records. >>
When no selection is made in the combobox, the combobox will
have a value of Null and the sewcond part of this criteria
will return all records.

With your code, maybe it works. But it's not what was wanted, an
"ALL" row in the combobox is part of the OP's specification. And
it will not work if there is a dsefault value set for the
combobox.
Per the OP --
<<one of my users wanted to see all of the values before choosing one of the
three selections.>>

There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from the
same place you learned to put calculated values in a table?
>
>Try it you arrogant $$Arse; it works!! Quit criticizing things
you don't understand!!!!

I'm not arrogant. I'm pedantic.
You hit the nail on the head!! You are certainly characterized by or given
to pretentious or conspicuous show in an attempt to impress others with your
ignorance.
I will thank you for having dropped the advertizing.
>>
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
>>"Steve" <so***@private.emailaddresswrote in
news:Rr*******************@newsread2.news.pas.ea rthlink.net:

Put the following in the vriteria:
[Forms]![Form_1]![Combo_1] Or ( [Forms]![Form_1]![Combo_1]
Is Null)

If you do not make a selection in the combobox, you will see
all the records.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel
And Word Applications
re******@pcdatasheet.com

Wrong answer again Steve. The O.P. doesn't want to handle
null, he wants to handle "ALL" as in
[Forms]![Form_1]![Combo_1] Or [Forms]![Form_1]![Combo_1]=
"ALL"

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jun 3 '07 #12

P: n/a
>With your code, maybe it works. But it's not what was wanted, an
"ALL" row in the combobox is part of the OP's specification. And
it will not work if there is a dsefault value set for the
combobox.

Per the OP --
<<one of my users wanted to see all of the values before choosing one of
the three selections.>>

There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from
the same place you learned to put calculated values in a table?
You are mistaken. I suggest you go back and read the original post. The
"All" choice is precisely what the OP requested.

Jun 3 '07 #13

P: n/a

"Arch" <no*****@thank.youwrote in message
news:uO*******************@newssvr13.news.prodigy. net...
>
>>With your code, maybe it works. But it's not what was wanted, an
"ALL" row in the combobox is part of the OP's specification. And
it will not work if there is a dsefault value set for the
combobox.

Per the OP --
<<one of my users wanted to see all of the values before choosing one of
the three selections.>>

There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from
the same place you learned to put calculated values in a table?

You are mistaken. I suggest you go back and read the original post. The
"All" choice is precisely what the OP requested.

There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from the
same place as Mr. Quintal?

Jun 3 '07 #14

P: n/a

"Bob Quintal" <rq******@sPAmpatico.caschreef in bericht news:Xn**********************@66.150.105.47...

I'm not arrogant. I'm pedantic.
I will thank you for having dropped the advertizing.
Hi Bob,

Why do you think he dropped the advertising ??
You mean: there was no sigline ??
Well, sometimes when he is angry he forgets to 'sign'...

He is playing the 'nicest' of all posters at the moment, especially in the ms-groups.
I am sure he will *really* advertise within a week or so again.

Correction: Just saw him doing *exactly that* at microsoft.public.access.tablesdbdesign ...
Hmmm, here we go again:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R
Jun 3 '07 #15

P: n/a
Quotes from the original posting:
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
My original 3 choices still work but not my "All". I have tried "Like
Reply from PC Datasheet, "The Application Resource":
There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from
the same place as Mr. Quintal?


Jun 4 '07 #16

P: n/a
"I" and "My" are not the user!!!

I repeat ----
Did you learn to read from the same place as Mr. Quintal?

Maybe you are pedantic too!!!
You are certainly characterized by or given to pretentious or conspicuous
show in an attempt to impress others with your ignorance.
"Arch" <no*****@thank.youwrote in message
news:tW*****************@newssvr27.news.prodigy.ne t...
Quotes from the original posting:
>I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
>My original 3 choices still work but not my "All". I have tried "Like

Reply from PC Datasheet, "The Application Resource":
>There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from
the same place as Mr. Quintal?



Jun 5 '07 #17

P: n/a
"Steve" <so***@private.emailaddresswrote
"I" and "My" are not the user!!!
They are used by the original poster in this thread, so it seems they refer
to that original poster. If the original poster had meant someone else, "I"
and "My" would have been inappropriate pronouns.

I'd be very appreciative if you would restrain your language. It is simple
to disagree without vulgarity, and, as well, your disagreement here is
silly.

Larry Linson
Jun 5 '07 #18

P: n/a
"Arch" <no*****@thank.youwrote in
news:tW*****************@newssvr27.news.prodigy.ne t:
Quotes from the original posting:
>I added the choice "All" to my combo dropdown list and tried
the following iif statement as the criteria:
>My original 3 choices still work but not my "All". I have
tried "Like

Reply from PC Datasheet, "The Application Resource":
>There is nothing saying the user wanted to see "All" as a
choice. Rather, the user wanted a way to see all the values.
Did you learn to read from the same place as Mr. Quintal?
I'd plonk PCD except somebody needs to correct the misinformation
he supplies to people with problems.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jun 6 '07 #19

This discussion thread is closed

Replies have been disabled for this discussion.