First and Last Name in Lookup field

  • Thread starter Thread starter NewAccessUser
  • Start date Start date
N

NewAccessUser

I created a table with 2 fields, FirstName and LastName. I
want to use these 2 fields in a lookup field in a second
table. I can create the lookup field with both the
LastName and the FirstName appearing when selecting
initially, but the FirstName disappears after selecting. I
want to see both names. I created a query of the 2 names
with sql and the query looks great. But I cannot get the
query to go in the lookup column. I get:
" is not a valid name. Make sure that it does not include
invalid characters or punctuation and that it is not too
long. (Error 3125).
When I look at the query in design view, the field shows
up as:
LastFirst: [tblPerson].[LastName]+[tblPerson].[FirstName]
How can I get the results I want? Thanks.
 
As part of your SQL, do you have something like. . .

[FirstName] & " " & [LastName] As TheName

??

HTH - Bob
 
I have:
[tblPerson].[LastName]+ ', ' + [tblPerson].[FirstName] as
LastFirst
-----Original Message-----
As part of your SQL, do you have something like. . .

[FirstName] & " " & [LastName] As TheName

??

HTH - Bob
-----Original Message-----
I created a table with 2 fields, FirstName and LastName. I
want to use these 2 fields in a lookup field in a second
table. I can create the lookup field with both the
LastName and the FirstName appearing when selecting
initially, but the FirstName disappears after selecting. I
want to see both names. I created a query of the 2 names
with sql and the query looks great. But I cannot get the
query to go in the lookup column. I get:
" is not a valid name. Make sure that it does not include
invalid characters or punctuation and that it is not too
long. (Error 3125).
When I look at the query in design view, the field shows
up as:
LastFirst: [tblPerson].[LastName]+[tblPerson].[FirstName]
How can I get the results I want? Thanks.
.
Click to expand...
.
Click to expand...
 
I created a table with 2 fields, FirstName and LastName. I
want to use these 2 fields in a lookup field in a second
table.
Click to expand...

See http://www.mvps.org/access/lookupfields.htm for a discussion of
this misfeature. Lookup fields are NEVER necessary (they can, once in
a great while, given careful design) be a little bit helpful, but
that's the most I can say for them.
I can create the lookup field with both the
LastName and the FirstName appearing when selecting
initially, but the FirstName disappears after selecting. I
want to see both names. I created a query of the 2 names
with sql and the query looks great.
Click to expand...

It's probably not possible with a table lookup; they are very limited
in their capabilities.

However, it's quite easy using a Combo Box (a "lookup" if you will) on
a Form, which is how you should be interacting with your data in any
case. Create a Query using a concatenation of firstname and lastname
as a calculated field:

SELECT PersonID, LastName & ", " & FirstName
FROM peopletable
ORDER BY LastName, FirstName;

and base a Combo Box on this query.
 
I noticed in the thread that you keep using the addition sign ("+") and the
responders keep using the ampersand ("&") to concatenate names. What
happens when you use "&"?

Also, how are you allowing for a row that has no entry for firstname --
doesn't your formula leave a comma and space "hanging"?

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top