IIF Function Within A Query

  • Thread starter Thread starter Bateman28
  • Start date Start date
B

Bateman28

Hi

I only have some experience of IIF statements so please bare with me. I am
trying to get the value of three fields into One field. The Problem I have
is, there are three fields that are either Yes Or No Fields. For Example here
are the only possible outcomes from any record displayed within a query with
the three field values.

Record1 Record2
Record3
Field1 = Yes Field1 = No
Field1 = No
Field2 = No Field2 = Yes
Field2 = No
Field3 = No Field3 = No
Field3 = Yes

I would like to create a new field Called "Work Due" that will be able to
pick up the Field data "Yes" from either of the three different Fields(1,2 &
3) from any record within the records returned by the query, so that when I
put the query into a form in datasheet view the form will only show 1 field
header ("Work Due") instead of 3 with the data from the field type where the
data equals "YES"

Example:

Work Due
Field 1 = Yes(The Yes Is the value the field Work Due is Looking For!)
Field 3 = Yes
Field 2 = Yes
Field 2 = Yes
Field 3 = Yes
Field 1 = Yes

And so on with every record.........If anyone could help it would be greatly
Appreciated as I’m starting to loose my hair!
 
On Wed, 17 Mar 2010 05:56:02 -0700, Bateman28

This is how I construct that. Start with one clause:
iif(Field1=Yes, "Field1", x)
Then replace X by a second clause. Literally replace x with a copy of
the expression, and replace 1 with 2:
iif(Field1=Yes, "Field1", iif(Field2=Yes, "Field2", x))
If Field2 is no, the only value left to set is "Field3":
iif(Field1=Yes, "Field1", iif(Field2=Yes, "Field2", "Field3))

-Tom.
Microsoft Access MVP
 
I'm confused by your sample data and desired display. Are they related?

I think you have an un-normalized table structure. You can normalize this
with a union query like:

SELECT PKField, "Field1" as WorkDue
FROM tblNoNameProvided
WHERE Field1 = -1
UNION ALL
SELECT PKField, "Field2"
FROM tblNoNameProvided
WHERE Field2 = -1
UNION ALL
SELECT PKField, "Field3"
FROM tblNoNameProvided
WHERE Field3 = -1;

The normalized data provides much greater flexibility for querying.
 
Thanks For the quick Response

I tried this:

Work Due: IIf([Service]=Yes,"Service",IIf([Mot]=Yes,"Mot",IIf([S &
Mot]=Yes,"ServiceMot")))

but the only return i get is "Service" in the "Work Due" field.

As i mentioned early i haven't really had much experience with IIf
formula's! I've never heard of a union query before so the chances of me
getting anywhere with that are very slim. I could research it on the web but
this is the only web page i am allowed access to but thanks for the
suggestion anyway, its appreciated.

Any Idea's, they would be welcomed with open arms!
 
Based on your Yes/No field names it looks like the choices are 'Service',
'Mot', and both. If this is correct then you should replace the three
fields with a number field and use an Option Group instead of check boxes.

--
Build a little, test a little.


Bateman28 said:
Thanks For the quick Response

I tried this:

Work Due: IIf([Service]=Yes,"Service",IIf([Mot]=Yes,"Mot",IIf([S &
Mot]=Yes,"ServiceMot")))

but the only return i get is "Service" in the "Work Due" field.

As i mentioned early i haven't really had much experience with IIf
formula's! I've never heard of a union query before so the chances of me
getting anywhere with that are very slim. I could research it on the web but
this is the only web page i am allowed access to but thanks for the
suggestion anyway, its appreciated.

Any Idea's, they would be welcomed with open arms!

Tom van Stiphout said:
On Wed, 17 Mar 2010 05:56:02 -0700, Bateman28

This is how I construct that. Start with one clause:
iif(Field1=Yes, "Field1", x)
Then replace X by a second clause. Literally replace x with a copy of
the expression, and replace 1 with 2:
iif(Field1=Yes, "Field1", iif(Field2=Yes, "Field2", x))
If Field2 is no, the only value left to set is "Field3":
iif(Field1=Yes, "Field1", iif(Field2=Yes, "Field2", "Field3))

-Tom.
Microsoft Access MVP



.
Click to expand...
Click to expand...
 
I thanks For you response

The three fields i'm working with are text fields with either "Yes" or "No"
in them. From the three field columns within 1 record there is always a
"Yes" outcome. Note:There is also only One "Yes" outcome so the other two
columns are always "No". So I am trying to get the "Yes" from which ever
Outcome of the three columns displays "Yes" to be displayed in the "Work
Due" column so that i can just have one column for what work is needed and
not three!

I tried this IIf Statement but it just returns "Service" and none of the
other Two Arguments. My knowledge on IIf statements is very small!!

See what you think

Work Due: IIf([Service]=Yes,"Service",IIf([Mot]=Yes,"Mot",IIf([S &
Mot]=Yes,"ServiceMot")))

Marshall Barton said:
Bateman28 said:
I only have some experience of IIF statements so please bare with me. I am
trying to get the value of three fields into One field. The Problem I have
is, there are three fields that are either Yes Or No Fields. For Example here
are the only possible outcomes from any record displayed within a query with
the three field values.

Record1 Record2
Record3
Field1 = Yes Field1 = No
Field1 = No
Field2 = No Field2 = Yes
Field2 = No
Field3 = No Field3 = No
Field3 = Yes

I would like to create a new field Called "Work Due" that will be able to
pick up the Field data "Yes" from either of the three different Fields(1,2 &
3) from any record within the records returned by the query, so that when I
put the query into a form in datasheet view the form will only show 1 field
header ("Work Due") instead of 3 with the data from the field type where the
data equals "YES"

Example:

Work Due
Field 1 = Yes(The Yes Is the value the field Work Due is Looking For!)
Field 3 = Yes
Field 2 = Yes
Field 2 = Yes
Field 3 = Yes
Field 1 = Yes

And so on with every record.........If anyone could help it would be greatly
Appreciated as I’m starting to loose my hair!
Click to expand...


If those are the only possible settings, then one of those
three fields will always be Yes and the WorkDone result will
always be Yes. Since that doesn't seem to make sense to me,
maybe you should try to explain more precisely what you are
trying to get the WorkDone field to display.

If all three fields might be No and that's what you are
checking for then the WorkDone fields expression could be:

WorkDone: IIf([field 1] = "Yes" OR [field 2] = "Yes" OR
[field 3] = "Yes", "Yes", "No")

If the three fields do not contain "Yes"/"No", but are
Yes/No fields that actually contain True/False, it would
simply be:
WorkDone: [field 1] OR [field 2] OR [field 3]
Click to expand...
 
The expression you are looking for is probably something like the following.

IIF(Field1="Yes","FieldOneIsYes", IIF(Field2="Yes","FieldTwoIsYes",
IIF(Field3="Yes","Feild3isYes",Null)))

You can replace FieldOneIsYes with whatever value you want to show.

It seems to me that you would be better off with one field (text) that
replaces all three of the other fields. You would have 4 choices for the text
field ChoiceA, ChoiceB, ChoiceC,NoChoiceYet.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I thanks For you response

The three fields i'm working with are text fields with either "Yes" or "No"
in them. From the three field columns within 1 record there is always a
"Yes" outcome. Note:There is also only One "Yes" outcome so the other two
columns are always "No". So I am trying to get the "Yes" from which ever
Outcome of the three columns displays "Yes" to be displayed in the "Work
Due" column so that i can just have one column for what work is needed and
not three!

I tried this IIf Statement but it just returns "Service" and none of the
other Two Arguments. My knowledge on IIf statements is very small!!

See what you think

Work Due: IIf([Service]=Yes,"Service",IIf([Mot]=Yes,"Mot",IIf([S &
Mot]=Yes,"ServiceMot")))

Marshall Barton said:
Bateman28 said:
I only have some experience of IIF statements so please bare with me. I am
trying to get the value of three fields into One field. The Problem I have
is, there are three fields that are either Yes Or No Fields. For Example here
are the only possible outcomes from any record displayed within a query with
the three field values.

Record1 Record2
Record3
Field1 = Yes Field1 = No
Field1 = No
Field2 = No Field2 = Yes
Field2 = No
Field3 = No Field3 = No
Field3 = Yes

I would like to create a new field Called "Work Due" that will be able to
pick up the Field data "Yes" from either of the three different Fields(1,2 &
3) from any record within the records returned by the query, so that when I
put the query into a form in datasheet view the form will only show 1 field
header ("Work Due") instead of 3 with the data from the field type where the
data equals "YES"

Example:

Work Due
Field 1 = Yes(The Yes Is the value the field Work Due is Looking For!)
Field 3 = Yes
Field 2 = Yes
Field 2 = Yes
Field 3 = Yes
Field 1 = Yes

And so on with every record.........If anyone could help it would be greatly
Appreciated as I’m starting to loose my hair!
Click to expand...

If those are the only possible settings, then one of those
three fields will always be Yes and the WorkDone result will
always be Yes. Since that doesn't seem to make sense to me,
maybe you should try to explain more precisely what you are
trying to get the WorkDone field to display.

If all three fields might be No and that's what you are
checking for then the WorkDone fields expression could be:

WorkDone: IIf([field 1] = "Yes" OR [field 2] = "Yes" OR
[field 3] = "Yes", "Yes", "No")

If the three fields do not contain "Yes"/"No", but are
Yes/No fields that actually contain True/False, it would
simply be:
WorkDone: [field 1] OR [field 2] OR [field 3]
Click to expand...
Click to expand...
 
Back
Top