using a IIF statement with a Like statement

  • Thread starter Thread starter Cathy
  • Start date Start date
C

Cathy

I would like to use an iif statement with a Like ... here is an example:

IIf([SenderName]=Like "AMEX",("AMEX")),IIf([SenderName]=Like
"Discover",("DISCO"))),[SenderName]="Visa MC"))))

I keep getting #NAME?
 
IIf([SenderName]=Like "AMEX",("AMEX")),IIf([SenderName]=Like
"Discover",("DISCO"))),[SenderName]="Visa MC"))))

= IIf([SenderName] LIKE "*AMEX*", "AMEX", IIf([SenderName] LIKE
"*Discover*","DISCO","Visa MC"))

Now, I wonder if you are using LIKE correctly? LIKE is use to match parts
of a string. But, with no wildcard characters, it will only find the exact
values. If what you are trying to do is looking for one of the 3 values, an
= sign would as well.
 
I would like to use an iif statement with a Like ... here is an example:

IIf([SenderName]=Like "AMEX",("AMEX")),IIf([SenderName]=Like
"Discover",("DISCO"))),[SenderName]="Visa MC"))))

I keep getting #NAME?
Click to expand...

The = operator is one operator; the LIKE operator is a different operator. You
should use one or the other, not both.

Secondly, the LIKE operator expects wildcards; if you don't have a wildcard (*
for any string of characters, ? for any single character, # for any digit,
etc.) in the criterion it works exactly the same as =.

What's in SenderName? Will it start with AMEX, end with AMEX, contain AMEX
buried somewhere within it, or what?
 
John - I guess I would need to use the Like operator and use the *Amex,
because the AMEX comes as the last part of the description in the sender
name. However I'm still having trouble linking the entire string for
Discover. I also forgot to add in that I have anything with 3 digits that
should be CASH, all others should be Visa/MC.

Would I use the # for anything with 3 digits? How would I write that?
--
Very grateful for help !


John W. Vinson said:
I would like to use an iif statement with a Like ... here is an example:

IIf([SenderName]=Like "AMEX",("AMEX")),IIf([SenderName]=Like
"Discover",("DISCO"))),[SenderName]="Visa MC"))))

I keep getting #NAME?
Click to expand...

The = operator is one operator; the LIKE operator is a different operator. You
should use one or the other, not both.

Secondly, the LIKE operator expects wildcards; if you don't have a wildcard (*
for any string of characters, ? for any single character, # for any digit,
etc.) in the criterion it works exactly the same as =.

What's in SenderName? Will it start with AMEX, end with AMEX, contain AMEX
buried somewhere within it, or what?
Click to expand...
 
John - I guess I would need to use the Like operator and use the *Amex,
because the AMEX comes as the last part of the description in the sender
name. However I'm still having trouble linking the entire string for
Discover. I also forgot to add in that I have anything with 3 digits that
should be CASH, all others should be Visa/MC.

Would I use the # for anything with 3 digits? How would I write that?
Click to expand...

I'm sorry, but you're assuming that I can see and understand the way you have
your data stored. I cannot and do not.

Please post some examples of the data that you're trying to parse. Why would
three digits be cash, for example...!? And what does "linking the entire
string" mean?

Just FWIW, to search for a string containing a three-digit substring you would
use

LIKE "*###*"
 
My apologies -
I have a field labeled Sender ID. The numbers that come into this field
vary depending on the payment type. The ones from Discover come in and start
with a 6, the ones from AMEX come in and start with a 4, the Cash comes in
and is only 3 digits long but start with a 1 or 2 or 3, the remaining are
VISA or MC and they start with 5, 6, 8 or 9... but never with 6011 like
discover.

here is what i have written - but it isn't working -
=IIf([SenderID] Like "4*",("AMEX")), IIf([SenderID] Like
"6011*",("DISCO"))), IIf ([SenderID] Like "###", ("CASH")))), "MC/VISA")))))

When I say linking the entire string I think I mean I was having trouble
putting commas and closing parenthesis in the right places, I am hoping that
is why it isn't working.
 
My apologies -
I have a field labeled Sender ID. The numbers that come into this field
vary depending on the payment type. The ones from Discover come in and start
with a 6, the ones from AMEX come in and start with a 4, the Cash comes in
and is only 3 digits long but start with a 1 or 2 or 3, the remaining are
VISA or MC and they start with 5, 6, 8 or 9... but never with 6011 like
discover.
Click to expand...

Try using the Switch() function instead of deeply nested IIF's. It takes
arguments in pairs and evaluates them left to right; when it first encounters
a pair with the first argument TRUE it returns the second member of that pair
and quits. So:

Switch([SenderID] LIKE "6011*", "DISC", [SenderID] LIKE "4*", "AMEX",
[SenderID] LIKE "[123]##", "Cash", [SenderID] LIKE "[5689]*", "MC/VISA", True,
"Invalid Entry")

This covers the possibility that a record will come in which doesn't match any
of your patterns - the last pair of arguments will only be tried if all the
others have failed, and since its first member is a literal True it will just
return the "Invalid Entry" message. I'm using the wildcard feature that lets
you include a list of values in square brackets - [5689] will match any of 5,
6, 8 or 9.
 
Hi Cathy
The code you posted had both equals and like together (=Like).
You can use = or you can use Like but not both together.

The code below uses the equals operator.
IIf([SenderName]="AMEX",("AMEX")),IIf([SenderName]=
"Discover",("DISCO"))),[SenderName]"Visa MC"))))

The code below uses the like operator.
IIf([SenderName] Like"AMEX*",("AMEX")),IIf([SenderName] Like
"Discover*",("DISCO"))),[SenderName]"Visa MC"))))



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Back
Top