Some SQL code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone help a novice with a problem he can't solve himself. My Access
table (called Results) has a field called Name, the format of which is
surname:first names (eg Jones:Jim). I want a query or a macro which will
update the field to First Names Surname (eg Jim Jones). I have been trying
to write some SQL code but without success. Any help would be much
appreciated.

I am using Access 2003 and Windows XP Professional

Jim Jones
Ministry of Education
Botswana
 
Jim,

Open a standard module (existing or new) and paste in the following code:

Function Invert_Name(nam)
sep = InStr(1, nam, ":")
If sep = 0 Then
Invert_Name = nam
Exit Function
End If
fnam = Right(nam, Len(nam) - sep)
lnam = Left(nam, sep - 1)
Invert_Name = fnam & " " & lnam
End Function

Save the module. Now make an update query on your table, and in the Update
To line under the Name field type:

Invert_Name([Name])

Run the query and the job is done! Just make sure you run it only once. In
any case, make a back-up copy of your table before you try anything!

HTH,
Nikos
 
Jim,

Make an Update Query based on your table. In the query design view, in
the Update To row of the [Name] field, put...
Mid([Name],InStr([Name],":")+1) & " " & Left([Name],InStr([Name],":")-1)

The SQL of this query will look like this...
UPDATE Results SET [Name] = Mid([Name],InStr([Name],":")+1) & " " &
Left([Name],InStr([Name],":")-1)

By the way, as an aside, the word Name is a Reserved Word (i.e. has a
special meaning) in Access, and as such should not be used as the name
of a field or control or database object... I suggest you change this.
 
Dear Nikos

Worked first time!

Many thanks

Jim Jones

Nikos Yannacopoulos said:
Jim,

Open a standard module (existing or new) and paste in the following code:

Function Invert_Name(nam)
sep = InStr(1, nam, ":")
If sep = 0 Then
Invert_Name = nam
Exit Function
End If
fnam = Right(nam, Len(nam) - sep)
lnam = Left(nam, sep - 1)
Invert_Name = fnam & " " & lnam
End Function

Save the module. Now make an update query on your table, and in the Update
To line under the Name field type:

Invert_Name([Name])

Run the query and the job is done! Just make sure you run it only once. In
any case, make a back-up copy of your table before you try anything!

HTH,
Nikos

Jim Jones said:
Can someone help a novice with a problem he can't solve himself. My Access
table (called Results) has a field called Name, the format of which is
surname:first names (eg Jones:Jim). I want a query or a macro which will
update the field to First Names Surname (eg Jim Jones). I have been trying
to write some SQL code but without success. Any help would be much
appreciated.

I am using Access 2003 and Windows XP Professional

Jim Jones
Ministry of Education
Botswana
Click to expand...
Click to expand...
 
Back
Top