Update query question

  • Thread starter Thread starter Mandi
  • Start date Start date
M

Mandi

I have a text field [name] with names like:
Doe, John
Smith, Jim

I also have 2 other text fields that are currently blank
[f_name] and [l_name]

How do I create an update query to put only the last name
in [l_name] and only the first name in [f_name] (from the
[name] field? (I know there is a function that will help
me do this...)

Any assistance is appreciated! Thanks!
 
Mandi said:
I have a text field [name] with names like:
Doe, John
Smith, Jim

I also have 2 other text fields that are currently blank
[f_name] and [l_name]

How do I create an update query to put only the last name
in [l_name] and only the first name in [f_name] (from the
[name] field? (I know there is a function that will help
me do this...)
Click to expand...

UPDATE YourTableName
SET f_name = Mid([name], InStr(1, [name], ",")+2),
l_name = Left([name], InStr(1, [name], ",")-1)

BTW One should never name any of your own fields or controls name. Since many Access
and VBA objects have a property named name, this can cause Access to get confused
about whether you mean the object named name or the property named name. (see how
confusing that looks?)

If that field is going to stick around (and it shouldn't) then change it to FullName
or similar.
 
Back
Top