Lookup Value In Expression

Question

Hi All

I hope that someone can help me. I am trying to do something that I am sure is really simple but I am new to Access and I just cant figure it out.

I want to build an email address out of 2 fields in my table but one of the fields is a lookup from another table so when I try to join the fields in either the expression builder or a macro I just get the ID value. for example ******@abc.com instead of ******@abc.com.

my table as as follows:

Users

UserID (AutoNumber)	FirstName (Text)	LastName (Tex)	Department (Number) (Lookup)	Email (Calculation)
1	Jo	Bloggs	Sales

Departments

DepartmentID (AutoNumber)	DepartmentName
1	Sales

The expression I have been using the calculation field is [FirstName] & "." & [Department] & "@abc.com"

If anyone can point me in the right direction that would be great.

Thank you in advance.

Answer 1

The same way you did in a query. Use the Same expression (only substitue Control names for field names) 

Use an Unbound with a controlshouce set to:

=[Firstname] & [cboDept].Column(1) & ".domain.com"

Or whatever the email address formula.

Answer 2

Hi Scott

Sorry but like I said I am new to access.

I have removed the lookup from the table and have now done this in the form. so that I have a combo box on the form which lists my departments from my departments table. I have also created the query to join the DepartmentName and the users FirstName name together and from this have created a new column to create the email address. So I now have the email address as I want it in the query.

What I now cant figure out is how to show this on the form.

Sorry again and thank you for the help.

Answer 3

First, Lookups should be done On the form level, not on the table level.

You will not be able to create a calculation field for this. But its easy to do in a query. Just join the employee table to the Department table and then enter your expression.