IIf expression help needed

[dmschulz]

I am trying to write an IIf statement using multiple text answers. Such as if
"yes" or "maybe" count each and if "no" ignore. When I use Or I get a
complete total regardless of the answer. If I use And I get the total zero.
This is what I have used- =Sum(IIf([NameOfField] = "Yes" Or "Maybe", 1, 0))
What am I doing wrong? Can I do this at all? If so what is the correct
expression? Thanks, Dawn

 

[BobT]

Your current test as you have written it is:

If NameOfField = "Yes" or "Maybe" - but "Maybe" is it's own test. What you
have really reads in English as:

If NameOfField = "Yes" or "Maybe" = True

What you really want is:

IIf(([NameOfField] = "Yes) or ([NameOfField] = "Maybe"), 1, 0)

 

[Tom van Stiphout]

On Tue, 6 Oct 2009 07:04:01 -0700, dmschulz

The proper syntax is to repeat the field you want to test:
IIf([NameOfField] = "Yes" Or [NameOfField] = "Maybe"

-Tom.
Microsoft Access MVP

 

[Stefan Hoffmann]

hi Dawn,

I am trying to write an IIf statement using multiple text answers. Such as if
"yes" or "maybe" count each and if "no" ignore. When I use Or I get a
complete total regardless of the answer. If I use And I get the total zero.
This is what I have used- =Sum(IIf([NameOfField] = "Yes" Or "Maybe", 1, 0))
What am I doing wrong? Can I do this at all? If so what is the correct
expression? Thanks, Dawn

Try the IN operator:

=Sum(IIf(Trim([NameOfField]) IN ("Yes", "Maybe"), 1, 0))


mfG
--> stefan <--

 

[dmschulz]

Thanks. I knew I was close but just couldn't get there alone. Thanks again.

Your current test as you have written it is:

If NameOfField = "Yes" or "Maybe" - but "Maybe" is it's own test. What you
have really reads in English as:

If NameOfField = "Yes" or "Maybe" = True

What you really want is:

IIf(([NameOfField] = "Yes) or ([NameOfField] = "Maybe"), 1, 0)

I am trying to write an IIf statement using multiple text answers. Such as if
"yes" or "maybe" count each and if "no" ignore. When I use Or I get a
complete total regardless of the answer. If I use And I get the total zero.
This is what I have used- =Sum(IIf([NameOfField] = "Yes" Or "Maybe", 1, 0))
What am I doing wrong? Can I do this at all? If so what is the correct
expression? Thanks, Dawn

 

[dmschulz]

Thank you so much Stefan. Worked perfectly.

hi Dawn,

I am trying to write an IIf statement using multiple text answers. Such as if
"yes" or "maybe" count each and if "no" ignore. When I use Or I get a
complete total regardless of the answer. If I use And I get the total zero.
This is what I have used- =Sum(IIf([NameOfField] = "Yes" Or "Maybe", 1, 0))
What am I doing wrong? Can I do this at all? If so what is the correct
expression? Thanks, Dawn

Try the IN operator:

=Sum(IIf(Trim([NameOfField]) IN ("Yes", "Maybe"), 1, 0))


mfG
--> stefan <--