IIF Statement to set Query Paramaters from a Form

[Guest]

The following query below works but I want the "45" part to be able to put in
the query "43" Or "45" as values. How do I make the query formula accept
multiple values?

SELECT [Q: Rework].Model, Sum([Q: Rework].SumOfTime) AS SumOfSumOfTime
FROM [Q: Rework]
WHERE ((([Q: Rework].Plant)=IIf([Forms]![F: Dates]![Report Footer]="RX
Rework",45,[Forms]![F: Dates]![Plant Code])))
GROUP BY [Q: Rework].Model;

 

[David Ferguson]

Use a nested Iif statement depending on what the criteria for displaying 43
will be

IIf([Forms]![F: Dates]![Report Footer]="RX Rework",45,IIf([Forms]![F:
Dates]![Report Footer]="NEW CRITERIA",43,[Forms]![F: Dates]![Plant Code]))

 

[Guest]

Let me try and put what I want in the SQL so you can see what I want.

SELECT [Q: Rework].Model, Sum([Q: Rework].SumOfTime) AS SumOfSumOfTime
FROM [Q: Rework]
WHERE ((([Q: Rework].Plant)=IIf([Forms]![F: Dates]![Report Footer]="RX
Rework",43 Or 45,[Forms]![F: Dates]![Plant Code])))
GROUP BY [Q: Rework].Model;

The "43 Or 45" part does not work

Use a nested Iif statement depending on what the criteria for displaying 43
will be

IIf([Forms]![F: Dates]![Report Footer]="RX Rework",45,IIf([Forms]![F:
Dates]![Report Footer]="NEW CRITERIA",43,[Forms]![F: Dates]![Plant Code]))

The following query below works but I want the "45" part to be able to put in
the query "43" Or "45" as values. How do I make the query formula accept
multiple values?

SELECT [Q: Rework].Model, Sum([Q: Rework].SumOfTime) AS SumOfSumOfTime
FROM [Q: Rework]
WHERE ((([Q: Rework].Plant)=IIf([Forms]![F: Dates]![Report Footer]="RX
Rework",45,[Forms]![F: Dates]![Plant Code])))
GROUP BY [Q: Rework].Model;

 

[David Ferguson]

I'm assuming that the value you want in the field is "43 Or 45"

If that is the case then you just need to enclose it in quotes within the
formula as I've shown in your formula below.

 

[Guest]

I did and that does not work. The 43 and 45 are separate values. I get the
error stating that the expression is typed incorrectly, or it is too complex
to be evaluated.

If I hard enter the numbers in the query (leaving out the whole where
statement) it does look like '43 Or 45' and the query takes them as separate
values, that is why I do not understand why it will not take it in the
formula.

I'm assuming that the value you want in the field is "43 Or 45"

If that is the case then you just need to enclose it in quotes within the
formula as I've shown in your formula below.

Let me try and put what I want in the SQL so you can see what I want.

SELECT [Q: Rework].Model, Sum([Q: Rework].SumOfTime) AS SumOfSumOfTime
FROM [Q: Rework]
WHERE ((([Q: Rework].Plant)=IIf([Forms]![F: Dates]![Report Footer]="RX
Rework","43 Or 45",[Forms]![F: Dates]![Plant Code])))
GROUP BY [Q: Rework].Model;

 

[David Ferguson]

I may be getting confused James but the way your query reads is as follows:

If the [Report Footer] field within the [F: Dates] form has a value of "RX
Rework" then the [Plant] field in [Q: Rework] should be "45" otherwise it
should be populated with the [Plant Code] field from the [F: Dates] form.

Are now you wanting your query to state that if the value of [Report Footer]
is "RX Rework" then the [Plant] field should be "43 Or 45" or do you want
the [Plant] field to show either "43" or "45".

I'm assuming that its the latter as the value in the [Plant] field is used
in the overall expression within the query, so if that is the case then you
should use another Iif statement within the original (See my first reply) so
that Access can decide whether to use 43 or 45, but for this you'll need to
supply another set of criteria to help it decide.

 

[Guest]

Maybe we both are confused. Me because of my lack of experience and you
because I am not communicating it well. Here goes. I am wanting my query to
state that if the value of [Report Footer] is "RX Rework" then the [Plant]
field should show either "43" or "45". If I use the Iif (Iif()) statement
can I use the same criteria as the true statement?

Below is what you wrote. If I read it correctly it says: if the footer
statement equals “RX Rework†is true then set the value to “43â€. If it is
false run the other Iif() statement.

IIf([Forms]![F: Dates]![Report Footer]="RX Rework",45,IIf([Forms]![F:
Dates]![Report Footer]="NEW CRITERIA",43,[Forms]![F: Dates]![Plant Code]))

What I need it to say is: if the footer statement “RX Rework†is true then
set the value to “43†or “45†(so it queries all plant codes either “43†or
“45â€). If it is false read the Plant code field. I have tried entering the
statement in the following ways and none of them work.

IIf([Forms]![F: Dates]![Report Footer]="RX Rework",([Q:
Rework].[Plant])="43" Or ([Q: Rework].[Plant])="45",[Forms]![F: Dates]![Plant
Code])

IIf([Forms]![F: Dates]![Report Footer]="RX Rework",((([Q:
Rework].Plant)=43)) OR ((([Q: Rework].Plant)=45)),[Forms]![F: Dates]![Plant
Code])

IIf([Forms]![F: Dates]![Report Footer]="RX Rework",(43) OR (45),[Forms]![F:
Dates]![Plant Code])

IIf([Forms]![F: Dates]![Report Footer]="RX Rework",â€43†OR “45â€,[Forms]![F:
Dates]![Plant Code])

IIf([Forms]![F: Dates]![Report Footer]="RX Rework",([Plant])="43" Or
([Plant])="45",[Forms]![F: Dates]![Plant Code])

I am sure there has to be a way that the criteria will take the multiple
values as an “Or†search for the criteria. If not, I may be stuck.

Thanks for your patience and your attempts to help.

I may be getting confused James but the way your query reads is as follows:

If the [Report Footer] field within the [F: Dates] form has a value of "RX
Rework" then the [Plant] field in [Q: Rework] should be "45" otherwise it
should be populated with the [Plant Code] field from the [F: Dates] form.

Are now you wanting your query to state that if the value of [Report Footer]
is "RX Rework" then the [Plant] field should be "43 Or 45" or do you want
the [Plant] field to show either "43" or "45".

I'm assuming that its the latter as the value in the [Plant] field is used
in the overall expression within the query, so if that is the case then you
should use another Iif statement within the original (See my first reply) so
that Access can decide whether to use 43 or 45, but for this you'll need to
supply another set of criteria to help it decide.

I did and that does not work. The 43 and 45 are separate values. I get the
error stating that the expression is typed incorrectly, or it is too complex
to be evaluated.

If I hard enter the numbers in the query (leaving out the whole where
statement) it does look like '43 Or 45' and the query takes them as separate
values, that is why I do not understand why it will not take it in the
formula.