Case function in Access?

[kcddoorman]

Ok, I have almost masted the IF and IIF functions but these take up to many fields in a query. Suppose I have 10 different sales people in my database that want to receive an e-mail when their orders are completed. I started out doing this using a command button that asks the user to enter the address, subject, and body. This worked fine but I want all that data entered automatically. So, I need something on the form or query that holds the e-mail address according to which salesperson it is. I went to what I know and that is IIf functions.
in the query:

 IIf ([salesperson] = "DB", "[e-mail]", "") 

that works fine as well except I have to make a field in the query for every one of my sales people.
What I want is one singel Field that test for all the possible abbreviated names and gives the correct e-mail address to the record. Like a IIf function with multiple conditions? I'm not sure if Access will take a case statement?
I just need the name of the function to use in the query. Or, should I being doing this all in VB?

[Rabbit]

If I understand you correctly, you can just make another table that holds Name and E-mail address values. And then you can have a query joined on Name.

[kcddoorman]

Connect the queries?
Here is the original code for entering the information.

Private Sub CMDemail_Click()
Dim strToWhom As String
Dim strSubject As String
Dim strBody As String
Dim strMsgBody As String
Dim intSeeOutlook As Integer
 
strToWhom = InputBox("Enter recipient's e-mail address.")
strSubject = InputBox("Enter Subject.")
strBody = InputBox("Enter Text.")
 
DoCmd.SendObject , , , strToWhom, , , strSubject, strBody, intSeeOutlook
x = MsgBox("Email has been sent")
 
End Sub 

Now I can make all of this work with data that is displayed in the form.
Something like this.

Private Sub CMDemail_Click()
Dim strToWhom As String
Dim strSubject As String
Dim strBody As String
Dim strMsgBody As String
Dim intSeeOutlook As Integer
 
'this associates only one abbreviation with an email address
IIf [salesperson] = "JB", strToWhom = "[e-mail]", ""
'the subject for this information
strSubject = "Order Ready"
'the information about the completed order(sytax is wrong ill fix it)
strBody = [CUSTOMERNAME], [WO NUMBER], "is completed"
 
DoCmd.SendObject , , , strToWhom, , , strSubject, strBody, intSeeOutlook
x = MsgBox("Email has been sent")
 
End Sub

I found this code on here by the way thanks.
There are 10 possible strings that can be put into [SALESPERSON] and 10 different e-mail addresses that go with them. So, if [salesperson] does not equal JB then it gives me an Outlook composed message with the address blank because it doesn't have the info to send yet.

I'm thinking I need look at something like a Loop or Case Function. I'll work on it tonight and report my findings this evening.

[Rabbit]

I misunderstood your original posting.
Sure, you could use a select case but that's a hassle to maintain.
Instead you could have a table that stores Name and E-mail information and use the DLookup function to find the e-mail address from the table.

DLoopup("EMail", "tbl_EMail", "Name = '" & [SalesPerson] & "'")

[NeoPa]

Rabbit, Always remember to use code tags. Notice how you can differentiate the different types of quotes in your example.

KC, listen to this guy, he's talking sense (again). I would have posted a response but I found it was already done fine. You need to include a table with this data in your design.