Table Concatenate Fields Access 2007

[sophia]

I have a table that has FirstName and LastName fields. I have another table
in which I would like to have a lookup field that will display the LastName,
FirstName. I tried to create a field "FullName" in the same table with the
FirstName and Last Name fields that would concatenate the two fields and then
i would point my lookup field to the "FullName" field.

So I put the following in the default value:

[Lnom] & ", " & [Fnom]

And Access keeps giving me the following error message:

The database engine does not recognize either the field "Lnom" in a
validation expression, or the default value in the table 'Names'.

 

[John W. Vinson]

I have a table that has FirstName and LastName fields. I have another table
in which I would like to have a lookup field that will display the LastName,
FirstName. I tried to create a field "FullName" in the same table with the
FirstName and Last Name fields that would concatenate the two fields and then
i would point my lookup field to the "FullName" field.

So I put the following in the default value:

[Lnom] & ", " & [Fnom]

And Access keeps giving me the following error message:

The database engine does not recognize either the field "Lnom" in a
validation expression, or the default value in the table 'Names'.

You cannot define a default value for a table field based on other fields in
that table, because a Default is applied the instant a new record is created -
and those other fields have no value at that instant.

You also should not store the fullname in any table whatsoever, as it can be
calculated when it's needed.

You may also want to reconsider using a Lookup Field *at all*: see
http://www.mvps.org/access/lookupfields.htm for a critique of what many
consider a misfeature.

You *can* and should use a Combo Box *ON A FORM* (not in a table), though. It
can be based on a Query (not on the names table):

SELECT PersonID, [LNom] & ", " & [FNom]
FROM peopletable
ORDER BY LNom, FNom;

to display the full name, but to store the foreign key to the primary key of
the peopletable.