iiF Statement

[Angel]

I have a query that its criteria is based on a combo box in a form. However
I have included an Item (All) in the list that I would like when selected,
display the entire contents in the table. I am trying an iif statement in
the query's criteria like this:
iif([Forms]![Item]![Itemcmb]="All",Is Not Null,Forms]![Item]![Itemcmb])
And of course is not working.
I have tried several other conbinations without any luck.
Can anyone help me on this?

 

[Gary Miller]

You are missing a bracket in front of the second Forms, but
that is not all of the problem. See if the following
variation works...

Like
IIf([Forms]![Item]![Itemcmb]="All","*",[Forms]![Item]![Itemc
mb])


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Tom Ellison]

Dear Angel:

You can perform what you want in other ways. How about this:

WHERE ([Forms]![Item]![temcmb] = "All" AND SomeField IS NOT NULL)
OR ([Forms]![Item]![temcmb] <> "All"
AND SomeField = ([Forms]![Item]![temcmb])

Doing more complex logic like this in the design grid can get mighty
nasty. That's one of the reasons I abandoned the grid and learned to
write SQL in text form. However, there is a middle approach. You can
work in the grid until you get to this point, switch to text format
and make these changes, then go back to the grid and see how it would
be done there.

Warning, this can also be extremely confusing, as well as
enlightening. Well, you can't win them all!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Angel]

Hey Tom I really appreciate your answer, However I have tried Gary's
suggestion and yours with out luck.
As you can probably tell I am not pretty savy at this. If you do not mind
can you embed your code into mine, perhaps I can learn by seeing how you
code it. Thank you.
Here it is :
SELECT dbo_AMC_ITEM_INV.ITEM, dbo_AMC_ITEM_INV.ITEM_DESC,
dbo_AMC_ITEM_INV.UM, dbo_AMC_ITEM_INV.STK_ROOM, dbo_AMC_ITEM_INV.BIN,
dbo_AMC_ITEM_INV.QTY_BY_LOC, dbo_AMC_ITEM_INV.RL_MAT_CST,
[QTY_BY_LOC]*[RL_MAT_CST] AS Total_Value, dbo_LOCATION_DESCRIPTION.LOC_DESC
FROM dbo_AMC_ITEM_INV INNER JOIN dbo_LOCATION_DESCRIPTION ON
(dbo_AMC_ITEM_INV.BIN = dbo_LOCATION_DESCRIPTION.BIN) AND
(dbo_AMC_ITEM_INV.STK_ROOM = dbo_LOCATION_DESCRIPTION.STK_ROOM)
WHERE
(((dbo_AMC_ITEM_INV.ITEM_DESC)=IIf([Forms]![Item]![Itemcmb].[value]<>"All",[
Forms]![Item]![Itemcmb],"*")));

Dear Angel:

You can perform what you want in other ways. How about this:

WHERE ([Forms]![Item]![temcmb] = "All" AND SomeField IS NOT NULL)
OR ([Forms]![Item]![temcmb] <> "All"
AND SomeField = ([Forms]![Item]![temcmb])

Doing more complex logic like this in the design grid can get mighty
nasty. That's one of the reasons I abandoned the grid and learned to
write SQL in text form. However, there is a middle approach. You can
work in the grid until you get to this point, switch to text format
and make these changes, then go back to the grid and see how it would
be done there.

Warning, this can also be extremely confusing, as well as
enlightening. Well, you can't win them all!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have a query that its criteria is based on a combo box in a form. However
I have included an Item (All) in the list that I would like when selected,
display the entire contents in the table. I am trying an iif statement in
the query's criteria like this:
iif([Forms]![Item]![Itemcmb]="All",Is Not Null,Forms]![Item]![Itemcmb])
And of course is not working.
I have tried several other conbinations without any luck.
Can anyone help me on this?

 

[Tom Ellison]

Dear Angel:

I'm giving it a try now:

SELECT A.ITEM, A.ITEM_DESC, A.UM, A.STK_ROOM, A.BIN, A.QTY_BY_LOC,
A.RL_MAT_CST, QTY_BY_LOC * RL_MAT_CST AS Total_Value, L.LOC_DESC
FROM dbo_AMC_ITEM_INV A
INNER JOIN dbo_LOCATION_DESCRIPTION L ON A.BIN = L.BIN
AND A.STK_ROOM = L.STK_ROOM
WHERE ([Forms]![Item]![Itemcmb] = "All" A.ITEM_DESC IS NOT NULL) OR
([Forms]![Item]![Itemcmb] <> "All" AND A.ITEM_DESC =
[Forms]![Item]![Itemcmb])

I've taken the liberty to give your long table names aliases. It's a
lot less to read through.

Please let me know if this helped!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hey Tom I really appreciate your answer, However I have tried Gary's
suggestion and yours with out luck.
As you can probably tell I am not pretty savy at this. If you do not mind
can you embed your code into mine, perhaps I can learn by seeing how you
code it. Thank you.
Here it is :
SELECT dbo_AMC_ITEM_INV.ITEM, dbo_AMC_ITEM_INV.ITEM_DESC,
dbo_AMC_ITEM_INV.UM, dbo_AMC_ITEM_INV.STK_ROOM, dbo_AMC_ITEM_INV.BIN,
dbo_AMC_ITEM_INV.QTY_BY_LOC, dbo_AMC_ITEM_INV.RL_MAT_CST,
[QTY_BY_LOC]*[RL_MAT_CST] AS Total_Value, dbo_LOCATION_DESCRIPTION.LOC_DESC
FROM dbo_AMC_ITEM_INV INNER JOIN dbo_LOCATION_DESCRIPTION ON
(dbo_AMC_ITEM_INV.BIN = dbo_LOCATION_DESCRIPTION.BIN) AND
(dbo_AMC_ITEM_INV.STK_ROOM = dbo_LOCATION_DESCRIPTION.STK_ROOM)
WHERE
(((dbo_AMC_ITEM_INV.ITEM_DESC)=IIf([Forms]![Item]![Itemcmb].[value]<>"All",[
Forms]![Item]![Itemcmb],"*")));

Dear Angel:

You can perform what you want in other ways. How about this:

WHERE ([Forms]![Item]![temcmb] = "All" AND SomeField IS NOT NULL)
OR ([Forms]![Item]![temcmb] <> "All"
AND SomeField = ([Forms]![Item]![temcmb])

Doing more complex logic like this in the design grid can get mighty
nasty. That's one of the reasons I abandoned the grid and learned to
write SQL in text form. However, there is a middle approach. You can
work in the grid until you get to this point, switch to text format
and make these changes, then go back to the grid and see how it would
be done there.

Warning, this can also be extremely confusing, as well as
enlightening. Well, you can't win them all!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have a query that its criteria is based on a combo box in a form. However
I have included an Item (All) in the list that I would like when selected,
display the entire contents in the table. I am trying an iif statement in
the query's criteria like this:
iif([Forms]![Item]![Itemcmb]="All",Is Not Null,Forms]![Item]![Itemcmb])
And of course is not working.
I have tried several other conbinations without any luck.
Can anyone help me on this?