Some SQL code

[Jim Jones]

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

 

[Nikos Yannacopoulos]

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

 

[Steve Schapel]

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.

 

[Jim Jones]

Dear Nikos

Worked first time!

Many thanks

Jim Jones

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

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