Update from other fields

S

Student Databaser

I want to populate the Trainer_ID field with 00+First three letters of field
first name +first three letters of field last name where Trainer_ID is Null

I think this would be similar to

UPDATE [Master Trainers] SET [Master Trainers].TRAINER_ID ="00"+((left(First
Name),3))+((left(Last Name),3))
WHERE ((([Master Trainers].TRAINER_ID) Is Null));

suggestions please :) Thanks
 
J

John Spencer

The only thing I see that might be amiss is that your field names have spaces
so they must include square brackets around them

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [Master Trainers]
SET [Master Trainers].TRAINER_ID =
"00" + LEFT([First Name],3) + LEFT([Last Name],3)
WHERE [Master Trainers].TRAINER_ID Is Null

One other problem is that you may get results like
OOJoOh if your trainer's first name is Jo and her last name is Oh.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
K

KARL DEWEY

Did you try it? Why not?

Always backup your database before doing any global update ---
UPDATE [Master Trainers] SET [Master Trainers].[TRAINER_ID] ="00" &
left([First Name],3) & left([Last Name],3)
WHERE [Master Trainers].[TRAINER_ID] Is Null;
 
Top