Concatenate a field to appear on a form

[snowboarder234]

I need the student's names to appear twice on the form: once in two searchable fields by both first and last name, and then in a second location on the same form in a concatenated string under the student's image.

The reason the names need to appear in two places is because the form is set up in tabs. Once the users moves to a different tab, other than the Student tab, the student's name no longer is readily visible on the form. For the benefit of the end user, I need a concatenated string to display at all times on a place on the form that is NOT in the tabs.

This is the error message I received when trying to set this up:


https://skydrive.live.com/redir?resi...MDMthVrXtvCNO8



My dilemma is I need to preserve the existing fields in the table that currently lists student in two fields by first and last name. I tried to add an additional field of first and last names to the same table (tblStudent) to concatenate and I obviously got the error message below. How else can I achieve the goal of have the first/last names appear as two searchable fields, and then have a concatenated first/last name field appear on the same form?

Graphic image link here:https://skydrive.live.com/redir?resi...D1v3B7DCFAjO4I

[pbaldy]

FYI, I deleted your duplicate thread. The error is because you have the same field in 2 different tables, so you have to specify the table, like:

TableName.FieldName

The image is hard to see, so perhaps the SQL of the query would be better.

[snowboarder234]

FYI, I deleted your duplicate thread. The error is because you have the same field in 2 different tables, so you have to specify the table, like:

TableName.FieldName

The image is hard to see, so perhaps the SQL of the query would be better.

SQL as per your request:
SELECT tblStudent.StudentID, tblStudent.GrantID, tblStudent.LastName AS tblStudent_LastName, tblStudent.FirstName AS tblStudent_FirstName, tblStudent.Project, tblStudent.UCDept, tblStudent.StudentWebPage, tblStudent.TItles_FK, tblStudent.WebsiteTypes_FK, tblStudent.EMail, tblStudent.AdvisorID_FK, tblStudent.CampusID_FK, tblStudent.Active, tblStudent.Interim, tblStudent.Final, tblStudent.Update, tblAdvisor.AdvisorID, tblAdvisor.LastName AS tblAdvisor_LastName, tblAdvisor.FirstName AS tblAdvisor_FirstName, tblAdvisor.Campus, tblAdvisor.AdvisorPg, tblStudent.Photo, tblStudent.Abstract, tblStudent.[LastName] & " " & [FirstName] AS Expr1, tblStudent.FirstName
FROM tblAdvisor INNER JOIN tblStudent ON tblAdvisor.[AdvisorID] = tblStudent.[AdvisorID_FK]
ORDER BY tblStudent.LastName;

Thank you!

[pbaldy]

You see the problem?

tblStudent.[LastName] & " " & [FirstName] AS Expr1

[snowboarder234]

You see the problem?

tblStudent.[LastName] & " " & [FirstName] AS Expr1

No, sorry, I don't.

[rzw0wr]

tblStudent.[LastName] & " " & [FirstName] AS Expr1

tblStudent.[LastName] & " " & tblStudent.[FirstName] as Expr1 - You also have tblAdvisor.FirstName in the query.
Access does not know which one you want to use.

Dale

[snowboarder234]

tblStudent.[LastName] & " " & tblStudent.[FirstName] as Expr1 - You also have tblAdvisor.FirstName in the query.
Access does not know which one you want to use.

Dale

Okay -- I selected the tblstudent. It lets me save the document, but gives me the same error message when I try to switch to datasheet view.
Link here: https://skydrive.live.com/redir?resid=67BF9D3CB1373507!218&authkey=!ACE5cHEE hTQIg3w

[pbaldy]

You have to type it into the expression.

[snowboarder234]

I didn't know that was required. Building the expression for this specific task is out of my scope. Can this be addressed in this forum or is it not permitted?

[pbaldy]

It's already been given to you:

tblStudent.[LastName] & " " & tblStudent.[FirstName] as Expr1

or in design view simply type in:

Expr1: tblStudent.[LastName] & " " & tblStudent.[FirstName]