the ever popular if>then statement

[sbrady19]

I am new to Access and need to know the answer to his question. Is it possible to use the if/then statement in this way.

if the "Last Name" field is "Womack" then
make the "email" files bob.womack@company.org



pretty simply, is this possible.

[JoeM]

Assuming "email" is a calculated field, then:

email: IIF([Last Name]="Womack","bob.womack@company.org","")

BTW, you didn't mention what should happen if the Last Name is not Womack. I have written it so that it returns nothing. If it should return something else, replace the last argument ("") with what it should return in that situation.

[sbrady19]

thanks for the code, I am a super new beginner. where does that code go. the previous field will be the the "last name" field so I will have a very long list of if's.
If email = womack, bob.womack@company.org
if email = smith, bob.smith@company.org
if email = brady, tom.brady@company.org
if email = jones, rufus.jones@company.org

you get the idea

[JoeM]

If that is the situation you have, you would NOT want to do this in an IF statement. What you want to do is set up a lookup table, that has the last name and the associated email address (hopefully, your Last Name field is unique or you are going to run into issues).

You would then just link your Main data table with this lookup table on the Last Name field, and return the email address. This is what relational databases do!

[sbrady19]

thanks joe, still need a lot more hand holding if you are willing to help. So I make a table with a last name column, and an email column, and if the lookup finds the last name that was entered in the 1 table in the 2nd table, then use the email associated with the 2nd table last name.

[JoeM]

Do you know how to join two tables together in a query?
You can find how to do that in any introductory book, or if you Google or YouTube it, you can find tutorials on how to do that.
Basically, you will add both tables to your query, join on the Last Name field, and return the fields from both tables that you want to see.

[sbrady19]

we have 2 people with the same last name.......brother and sister. what to do.

[sbrady19]

looks like what I want to do is not possible. I have a table and a Form based on the table. I would like for the email to automatically have the 1st name and last name and @company.com be entered for me as soon as I enter the users 1st and last name. I don't want to have to generate a query to do this. Like in a spreadsheet, A1=2 B1=2 C1 will always auto calc the result of A1+B1.

[John_G]

It's easily done with one line of code:

[emailaddress] = [FirstName] & "." & [LastName] & "@company.org"

Replace the control names with the names you use, and put the code in the After Update event of [LastName] on the form.

[June7]

There is no need for code to save. This concatenated value can be calculated whenever needed.

Every email address will have the same company name - your company?

[sbrady19]

thanks john, can you tell me where I put your code, I'm thinking under the default value

[June7]

Did you read post 10?

No, default value property will not work for this.

Code would have to be in the AfterUpdate event of LastName and FirstName (just in case one or both is changed) or the BeforeUpdate event of form.

[sbrady19]

if I could kindly ask............where would I find the AfterUpdate. I am super new to using Access. Thank you so very much.

[John_G]

There are many (dozens) events that occur in MS Access. The After Update is an event that occurs after you update a form control (or the form). Although the event always happens, you can control what actions Access takes by using VBA code (or macros)

To see the list of events applicable to a form control:

- In form design view, Right-Click the control you want to attach code to (in your case, the LastName)
- From the menu that is displayed, click "Properties" (at the bottom)
- click the "Event" tab; you will see a list of events that apply to that control - one of them is After Update
- click in the blank field beside "After Update"
- click the small down arrow at the right of the field, then select" Event Procedure" from the dropdown
- Click the "..." button to open the VBA code editing window; it will look like this (with a different procedure name):

Private Sub txtExcelfilename_AfterUpdate()

End Sub

- enter the one-line of code we showed you (with the correct names) between the two existing lines, close the code window, and save the changes to the form.

You would do the same for any controls and events on the form.

[sbrady19]

here is a tutorial I found. I did exactly as it says, and as people here have said to try and help me. didnt work. Newbie, what do you expect.
https://www.youtube.com/watch?v=KFMrSbmsrg4

I was in the form, in design mode, I went to the lastName field, went to properties, event, after event and entered this:
Prod1_Email = Prod1_1stName & "." & Prod1_LastName & "@xyz.org"

I would expect the email field to populate with the text from 1stname and lastname........but it didnt. aaarrggghhhhh