Nested IIFs

seamus99
seamus99 used Ask the Experts™
on
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????

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
I hate nested IIf, preferring Switch instead:


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".
Top Expert 2010

Commented:
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
Top Expert 2016

Commented:
try this

Switch([ClientName] Like "[A-CE]*","Max_swafford@aaa.com",[ClientName] Like "[CF–GZ]*","Joel_crane@aaa.com",[ClientName] Like "[H-NE]*","blonde_tipton@aaa.com",True,"Other")
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
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.
Top Expert 2016

Commented:
you're right Jez...

??
Top Expert 2016

Commented:
this will work but it will be complicated

Switch([ClientName] Like "[A-B]*","Max_swafford@aaa.com",[ClientName] Like "C[!D-Z]*","Max_swafford@aaa.com")

Commented:
Apart from that (arguably) not being as extensible as using a table, what happened to joel_crane@aaa.com and blonde_tipton@aaa.com?
Top Expert 2016

Commented:
that is  just a sample Jez... and i don't intend to suggest using that because of the complexity.

Author

Commented:
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

Commented:
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.
Top Expert 2010

Commented:
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
Top Expert 2010
Commented:
Slight change in approach.  Please see the attached database:
  • Two tables, tblClients and tblClientAssignments
  • One form, frmClientAssignments
  • The form is based on tblClients, with an unbound field, AssignEmail, using control source:
    =DLookUp("AssignEmail","tblClientAssignments","[StartWith] <= '" & [ClientName] & "' And '" & [ClientName] & "' < [EndWith]")

As you add new clients, that automatically "asks" the assignments table who is covering that range of names.

Q-25748573.mdb

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial