Basic IIf question

[Guest]

Hello,

2 part question....I know how to use a basic IIf statement in a query such
as below:

Dinner:
(IIf([RSVP_CODE]='ACP','Accepted',(IIf([RSVP_CODE]='DCL','Declined'))))

HOWEVER, I don't know how to make it work when I don't want to recognize the
entire RSVP_CODE, say I want to use the same function as above, but for all
entries in RSVP_CODE that just begin with A, I need to show as accepted. I
tried 'A*' but really knowing it wouldn't work because of the ' '. I tried
without ' ' and got an error.

Second part. In addition to statement above, When RSVP_CODE has no value or
entry, I need it to say 'NoResponse'.

I wasn't sure how to search my question, so I don't know if something like
this has previously been asked, if so, my apologies.

Thanks in advance for your help.
-Valerie

 

[Guest]

Oh, Another part...if the entry in RSVP_CODE begins with any other letter
other than A or D, in addition to if it is empty or null (as mentioned
below), I need it to say 'NoResponse' as well.

 

[Tom Lake]

Hello,

2 part question....I know how to use a basic IIf statement in a query such
as below:

Dinner:
(IIf(Left([RSVP_CODE],
1)='A','Accepted',(IIf([RSVP_CODE]='DCL','Declined'))))

HOWEVER, I don't know how to make it work when I don't want to recognize
the
entire RSVP_CODE, say I want to use the same function as above, but for
all
entries in RSVP_CODE that just begin with A, I need to show as accepted.
I
tried 'A*' but really knowing it wouldn't work because of the ' '. I
tried
without ' ' and got an error.

Second part. In addition to statement above, When RSVP_CODE has no value
or
entry, I need it to say 'NoResponse'.

For more than two choices, I'd use a VBA function rather than an IIf
function.

Function RSVP_Response(RSVP_Code)
If Left(RSVP_Code, 1) = "A" Then
RSVP_Response = "Accepted"
ElseIf RSVP_Code = "DCL" Then ' Use Left(RSVP_Code, 1) = "D" if you
just want the first letter
RSVP_Response = "Accepted"
ElseIf IsNull(RSVP_Code) Then
RSVP_Response = "No Response"
End If
End Function

Tom Lake

 

[John Spencer]

IIF(RSVP_Code Like "A*", "Accepted", IIF(RSVP_CODE="DCL","Declined","No
response"))

Oh, Another part...if the entry in RSVP_CODE begins with any other letter
other than A or D, in addition to if it is empty or null (as mentioned
below), I need it to say 'NoResponse' as well.

Hello,

2 part question....I know how to use a basic IIf statement in a query
such
as below:

Dinner:
(IIf([RSVP_CODE]='ACP','Accepted',(IIf([RSVP_CODE]='DCL','Declined'))))

HOWEVER, I don't know how to make it work when I don't want to recognize
the
entire RSVP_CODE, say I want to use the same function as above, but for
all
entries in RSVP_CODE that just begin with A, I need to show as accepted.
I
tried 'A*' but really knowing it wouldn't work because of the ' '. I
tried
without ' ' and got an error.

Second part. In addition to statement above, When RSVP_CODE has no value
or
entry, I need it to say 'NoResponse'.

I wasn't sure how to search my question, so I don't know if something
like
this has previously been asked, if so, my apologies.

Thanks in advance for your help.
-Valerie

 

[Guest]

Hi Tom, thank you for your response! I don't know VBA at all, but was able
to apply some of your example. I'm almost there, except I need to know how
to tell it to assign a value of '0', if it doesn't meet any of the two
criteria as listed below. Ideas?

Dinner RSVP:
(IIf(Left([TTD_COMMENT],2)="Gu",'2',(IIf(Left([TTD_COMMENT],2)="No",'1'))))

Thanks in advance!!
-Valerie

Hello,

2 part question....I know how to use a basic IIf statement in a query such
as below:

Dinner:
(IIf(Left([RSVP_CODE],
1)='A','Accepted',(IIf([RSVP_CODE]='DCL','Declined'))))

HOWEVER, I don't know how to make it work when I don't want to recognize
the
entire RSVP_CODE, say I want to use the same function as above, but for
all
entries in RSVP_CODE that just begin with A, I need to show as accepted.
I
tried 'A*' but really knowing it wouldn't work because of the ' '. I
tried
without ' ' and got an error.

Second part. In addition to statement above, When RSVP_CODE has no value
or
entry, I need it to say 'NoResponse'.

For more than two choices, I'd use a VBA function rather than an IIf
function.

Function RSVP_Response(RSVP_Code)
If Left(RSVP_Code, 1) = "A" Then
RSVP_Response = "Accepted"
ElseIf RSVP_Code = "DCL" Then ' Use Left(RSVP_Code, 1) = "D" if you
just want the first letter
RSVP_Response = "Accepted"
ElseIf IsNull(RSVP_Code) Then
RSVP_Response = "No Response"
End If
End Function

Tom Lake

 

[Tom Lake]

Oops! The second "Accepted" below should, of course be "Declined"

Tom Lake