Lookup Fields

[Pastor Rod]

I need Access to lookup "First Name" and "Last Name" in a Table (Students)
and insert the result into the field "Name" in the same table (ie Joe -
'First Name', Bloggs - 'Last Name' = 'Name' - Bloggs, Joe ) So I can use this
for sorting and drop in boxes.

 

[Allen Browne]

Don't. Too much maintenance.

Instead, use a query as the RowSource for the query. This example puts the
StudentID in the first column, and then the combined names in the visible
column:

SELECT [StudentID], Students.[First Name] & " " & Students.[Last Name] AS
FullName
FROM Students ORDER BY [First Name], [Last Name];

For surname first, use:

SELECT [StudentID], Students.[Last Name] & ", " + Students.[First Name] AS
FullName
FROM Students ORDER BY [Last Name], [First Name];

 

[Bob Miller]

I wouldn't do this in a table. Use a query and base your sorting an
drop boxes on the query. Place a field in your query:
Name:[Last Name] & ", " & [First Name]
Also, consider changing your field names to FirstName and LastName. I
is jsut easier in the long run to not have spaces in field names for
number of reasons, one being you don't have to place brackets aroun
them as I did in the above.

 

[Pastor Rod]

Bob THanks again. THis does a fantastic Job dropping the value in the form at
the time of entry what is just what I was looking for, however it does not
seem to be putting it into the 'Student' table. I use this data for drop
boxes in the form. Can you help me correct this?

I wouldn't do this in a table. Use a query and base your sorting and
drop boxes on the query. Place a field in your query:
Name:[Last Name] & ", " & [First Name]
Also, consider changing your field names to FirstName and LastName. It
is jsut easier in the long run to not have spaces in field names for a
number of reasons, one being you don't have to place brackets around
them as I did in the above.

I need Access to lookup "First Name" and "Last Name" in a Table
(Students)
and insert the result into the field "Name" in the same table (ie Joe
-
'First Name', Bloggs - 'Last Name' = 'Name' - Bloggs, Joe ) So I can
use this
for sorting and drop in boxes.

 

[Bob Miller]

My advice HAS to be coupled with Allen's. DON'T put a calculated field
i.e Name in a table that has LastName and FirstName. It isn't
necessary. You can always access the data from the table via a query
from which you can use for drop boxes in the form. Think about what
would happen is a student changed their name (a female got married, you
realized you misspelled a name (Joni not Joanie), etc.) You change the
last name in the Student table and it changes in the query, however,
the calculated field in the table doesn't change.

Bob THanks again. THis does a fantastic Job dropping the value in the
form at
the time of entry what is just what I was looking for, however it does
not
seem to be putting it into the 'Student' table. I use this data for
drop
boxes in the form. Can you help me correct this?

:


I wouldn't do this in a table. Use a query and base your sorting and
drop boxes on the query. Place a field in your query:
Name:[Last Name] & ", " & [First Name]
Also, consider changing your field names to FirstName and LastName.
It
is jsut easier in the long run to not have spaces in field names for
a
number of reasons, one being you don't have to place brackets around
them as I did in the above.
Pastor Rod Wrote:
I need Access to lookup "First Name" and "Last Name" in a Table
(Students)
and insert the result into the field "Name" in the same table (ie
Joe
-
'First Name', Bloggs - 'Last Name' = 'Name' - Bloggs, Joe ) So I can
use this
for sorting and drop in boxes.

 

[rigby]

Hi

I have a similar problem. But my LastName, FirstName lists are on a seperate
table. I tried using your criteria in a new query but it gives me an error
message regarding the syntax 'SELECT'. any ideas??

Rigby
Thanks

Don't. Too much maintenance.

Instead, use a query as the RowSource for the query. This example puts the
StudentID in the first column, and then the combined names in the visible
column:

SELECT [StudentID], Students.[First Name] & " " & Students.[Last Name] AS
FullName
FROM Students ORDER BY [First Name], [Last Name];

For surname first, use:

SELECT [StudentID], Students.[Last Name] & ", " + Students.[First Name] AS
FullName
FROM Students ORDER BY [Last Name], [First Name];

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need Access to lookup "First Name" and "Last Name" in a Table (Students)
and insert the result into the field "Name" in the same table (ie Joe -
'First Name', Bloggs - 'Last Name' = 'Name' - Bloggs, Joe ) So I can use
this
for sorting and drop in boxes.

 

[Allen Browne]

Create a query that does what you want.

Then switch it to SQL View (View menu, from query design.)

You are now looking at an example of the query statement you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a similar problem. But my LastName, FirstName lists are on a
seperate
table. I tried using your criteria in a new query but it gives me an error
message regarding the syntax 'SELECT'. any ideas??

Rigby
Thanks

Don't. Too much maintenance.

Instead, use a query as the RowSource for the query. This example puts
the
StudentID in the first column, and then the combined names in the visible
column:

SELECT [StudentID], Students.[First Name] & " " & Students.[Last Name] AS
FullName
FROM Students ORDER BY [First Name], [Last Name];

For surname first, use:

SELECT [StudentID], Students.[Last Name] & ", " + Students.[First Name]
AS
FullName
FROM Students ORDER BY [Last Name], [First Name];

I need Access to lookup "First Name" and "Last Name" in a Table
(Students)
and insert the result into the field "Name" in the same table (ie Joe -
'First Name', Bloggs - 'Last Name' = 'Name' - Bloggs, Joe ) So I can
use
this for sorting and drop in boxes.

 

[rigby]

Allen, Thank you

I managed to get the query to work fine (thanks to your help), BUT now i
need to implement it into a field on a form, so once i have typed in an ID
number in the one field, the next field will produce the lastname, firstname
relevant to the unique ID number entered.

i keep getting an error message in the field saying: #Name?
any ideas??
Thanks

Create a query that does what you want.

Then switch it to SQL View (View menu, from query design.)

You are now looking at an example of the query statement you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a similar problem. But my LastName, FirstName lists are on a
seperate
table. I tried using your criteria in a new query but it gives me an error
message regarding the syntax 'SELECT'. any ideas??

Rigby
Thanks

Don't. Too much maintenance.

Instead, use a query as the RowSource for the query. This example puts
the
StudentID in the first column, and then the combined names in the visible
column:

SELECT [StudentID], Students.[First Name] & " " & Students.[Last Name] AS
FullName
FROM Students ORDER BY [First Name], [Last Name];

For surname first, use:

SELECT [StudentID], Students.[Last Name] & ", " + Students.[First Name]
AS
FullName
FROM Students ORDER BY [Last Name], [First Name];

I need Access to lookup "First Name" and "Last Name" in a Table
(Students)
and insert the result into the field "Name" in the same table (ie Joe -
'First Name', Bloggs - 'Last Name' = 'Name' - Bloggs, Joe ) So I can
use
this for sorting and drop in boxes.

 

[Allen Browne]

There are too many unknowns to be able to give you an answer to this
question.

#Name means that Access can't make sense of what you are referring to in the
Control Source of your control. If it is a field, it may be misspelled. If
it is an expression, it must start with =, and all the names have to make
sense to Access. Square brackets are needed if the names contain spaces, or
other strange characters.

It is also unclear whether you are trying to navigate to record (find a
match), or copy the fields from somewhere into your form. In general, you
don't want to copy the fields from one table to another.

 

[rigby]

firstly, thank you for your time and help.

here it is in full...
Status (table): 'Surname' field; 'First Name' field; 'ID no' primary index
field

M/V Alaska (table): 'ID no' field; 'Security Officer' field.

I want my form (also named M/V Alaska) to in produce the 'Security Officer'
field, the 'Surname, First Name' answer relevant to the unique ID no which i
entre in the previous field. so i want it to extract the details from the
'status' table.
Does that make sense? I am new and so confused by this problem. if you need
more info, please ask.
Thank you
Rigby

 

[Allen Browne]

I see you have posted this as a separate thread in other groups, so will
leave the discussion to there.