seamus99
asked on
Nested IIFs
I have a form that my user inputs a company name, based on that name I want to populate another field (ContactName) in a related table.
The company names are endless and are added to monthly however the contact names are organized by a range of Client Names. Here is the data.
Client Names A – CE
Max_swafford@aaa.com
Client Names CF – GZ
Joel_crane@aaa.com
Client Names H - NE
blonde_tipton@aaa.com
So If the user enters the client name of Happy Place, I want to populate a table called AAA Customers with "Blonde_tipton@aaa.com.
Is there a way in an IIF satement to evaluate a text string by saying "starts with", like iif Client Begins with H - NE????
The company names are endless and are added to monthly however the contact names are organized by a range of Client Names. Here is the data.
Client Names A – CE
Max_swafford@aaa.com
Client Names CF – GZ
Joel_crane@aaa.com
Client Names H - NE
blonde_tipton@aaa.com
So If the user enters the client name of Happy Place, I want to populate a table called AAA Customers with "Blonde_tipton@aaa.com.
Is there a way in an IIF satement to evaluate a text string by saying "starts with", like iif Client Begins with H - NE????
All that said, I think you will be much better off putting this into a table:
tblClientAssignments
--------------------------
ClientAssignID (PK)
StartWith
EndWith
AssignEmail
Populate that with:
1, "0", "CF", "email1"
2, "CF", "H", "email2"
3, "H", "NF", "email3"
4, "NF", "ZZZZZZZZZZZZZ", "email4"
Now, to get the assignment:
SELECT c.ClientName, a.AssignEmail
FROM tblClients c INNER JOIN
tblClientAssignments a ON c.ClientName >= a.StartWith And c.ClientName < a.EndWith
tblClientAssignments
--------------------------
ClientAssignID (PK)
StartWith
EndWith
AssignEmail
Populate that with:
1, "0", "CF", "email1"
2, "CF", "H", "email2"
3, "H", "NF", "email3"
4, "NF", "ZZZZZZZZZZZZZ", "email4"
Now, to get the assignment:
SELECT c.ClientName, a.AssignEmail
FROM tblClients c INNER JOIN
tblClientAssignments a ON c.ClientName >= a.StartWith And c.ClientName < a.EndWith
try this
Switch([ClientName] Like "[A-CE]*","Max_swafford@aa
Switch([ClientName] Like "[A-CE]*","Max_swafford@aa
If you go with matthewspatrick's suggestion you'll end up with a scalable solution, that can easily be adapted when the set of companies changes.
No offense to capricorn1, but the "Like" operator usage he proposes won't match ranges as you require.
No offense to capricorn1, but the "Like" operator usage he proposes won't match ranges as you require.
you're right Jez...
??
??
this will work but it will be complicated
Switch([ClientName] Like "[A-B]*","Max_swafford@aaa
Switch([ClientName] Like "[A-B]*","Max_swafford@aaa
Apart from that (arguably) not being as extensible as using a table, what happened to joel_crane@aaa.com and blonde_tipton@aaa.com?
that is just a sample Jez... and i don't intend to suggest using that because of the complexity.
ASKER
ClientAssignID (PK)
StartWith
EndWith
AssignEmail
Populate that with:
1, "0", "CF", "email1"
2, "CF", "H", "email2"
3, "H", "NF", "email3"
4, "NF", "ZZZZZZZZZZZZZ", "email4"
Now, to get the assignment:
WHere do I put this query?
And What is the CLientName and AssignEmail? are those fields on the form?
SELECT c.ClientName, a.AssignEmail
FROM tblClients c INNER JOIN
tblClientAssignments a ON c.ClientName >= a.StartWith And c.ClientName < a.EndWith
StartWith
EndWith
AssignEmail
Populate that with:
1, "0", "CF", "email1"
2, "CF", "H", "email2"
3, "H", "NF", "email3"
4, "NF", "ZZZZZZZZZZZZZ", "email4"
Now, to get the assignment:
WHere do I put this query?
And What is the CLientName and AssignEmail? are those fields on the form?
SELECT c.ClientName, a.AssignEmail
FROM tblClients c INNER JOIN
tblClientAssignments a ON c.ClientName >= a.StartWith And c.ClientName < a.EndWith
Using the Customers table of Northwind and running this query:
SELECT CompanyName FROM Customers WHERE CompanyName BETWEEN "Bo*" AND "Er*";
starts correctly but is one short - most of the time. Can anyone explain why? You can use anything you like in the two between strings - of course you need the double quotes and the asterisk. Using "Bl*" AND "Du*", it is correct - but when I remove the space between 'Du' and 'monde' in the table it misses the last one again. I'm puzzled.
SELECT CompanyName FROM Customers WHERE CompanyName BETWEEN "Bo*" AND "Er*";
starts correctly but is one short - most of the time. Can anyone explain why? You can use anything you like in the two between strings - of course you need the double quotes and the asterisk. Using "Bl*" AND "Du*", it is correct - but when I remove the space between 'Du' and 'monde' in the table it misses the last one again. I'm puzzled.
Assuming you have a form for your clients, then base your form on a query like this:
SELECT <list of columns from tblClients>, a.AssignEmail
FROM tblClients c INNER JOIN
tblClientAssignments a ON c.ClientName >= a.StartWith And c.ClientName < a.EndWith
SELECT <list of columns from tblClients>, a.AssignEmail
FROM tblClients c INNER JOIN
tblClientAssignments a ON c.ClientName >= a.StartWith And c.ClientName < a.EndWith
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Switch([ClientName] < "CF", "Max_swafford@aaa.com", [ClientName] < "H", "Joel_crane@aaa.com", [ClientName] < "NF", "blonde_tipton@aaa.com", True, "somebody_else@aaa.com)
Note that by forcing the last term to always be true, you make that the "catch all".