If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
using a IIF statement with a Like statement
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? -- Very grateful for help ! |
#2
|
|||
|
|||
using a IIF statement with a Like statement
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. -- Dave Hargis, Microsoft Access MVP "Cathy" wrote: 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? -- Very grateful for help ! |
#3
|
|||
|
|||
using a IIF statement with a Like statement
On Tue, 6 Oct 2009 12:09:01 -0700, Cathy
wrote: 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? 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 W. Vinson [MVP] |
#4
|
|||
|
|||
using a IIF statement with a Like statement
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" wrote: On Tue, 6 Oct 2009 12:09:01 -0700, Cathy wrote: 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? 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 W. Vinson [MVP] |
#5
|
|||
|
|||
using a IIF statement with a Like statement
On Wed, 7 Oct 2009 05:14:01 -0700, Cathy
wrote: 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? 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 "*###*" -- John W. Vinson [MVP] |
#6
|
|||
|
|||
using a IIF statement with a Like statement
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. -- Very grateful for help ! "John W. Vinson" wrote: On Wed, 7 Oct 2009 05:14:01 -0700, Cathy wrote: 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? 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 "*###*" -- John W. Vinson [MVP] |
#7
|
|||
|
|||
using a IIF statement with a Like statement
On Wed, 7 Oct 2009 11:31:01 -0700, Cathy
wrote: 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. 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. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
using a IIF statement with a Like statement
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 "Cathy" wrote in message ... 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? -- Very grateful for help ! |
Thread Tools | |
Display Modes | |
|
|