update query - replace x number of characters in ()

L

Lewis Brunton

Hi,

I have a table worth of data with Surnames in it, and some of the surnames
are suffixed by various endings such as:

Smith(VERIFIER)
Jones(MODEL)

etc etc

I can create an update and replace query that removes the specific ending,
e.g.:

UPDATE AddressDetails SET AddressDetails.surname =
Replace([surname],"(MODEL)","")
WHERE (((AddressDetails.surname) Is Not Null));

But I would much rather have a query that removes any ending with brackets.
I have tried replacing the query with:

UPDATE AddressDetails SET AddressDetails.surname = Replace([surname],"(*)","")
WHERE (((AddressDetails.surname) Is Not Null));

But the * wildcard doesn't seem to work in this context.

Can anyone help?

thanks

Lewis Bruntin
 
S

SteveS

Lewis Brunton said:
Hi,

I have a table worth of data with Surnames in it, and some of the surnames
are suffixed by various endings such as:

Smith(VERIFIER)
Jones(MODEL)

etc etc

I can create an update and replace query that removes the specific ending,
e.g.:

UPDATE AddressDetails SET AddressDetails.surname =
Replace([surname],"(MODEL)","")
WHERE (((AddressDetails.surname) Is Not Null));

But I would much rather have a query that removes any ending with brackets.
I have tried replacing the query with:

UPDATE AddressDetails SET AddressDetails.surname = Replace([surname],"(*)","")
WHERE (((AddressDetails.surname) Is Not Null));

But the * wildcard doesn't seem to work in this context.

Can anyone help?

thanks

Lewis Bruntin
Click to expand...

Lewis,

I couldn't get a query to parse the surname field either. But I only spent
about 3 minutes trying.

It was much easier to whip out a little code....

I put the code in a standard module and called it from the click event of a
button, but you could just paste the code in the button click event, also.

Note: you might want to add error checking..
Watch for line wrap...

'------ BEGIN CODE --------
Dim rst As DAO.Recordset
Dim pos As Integer, Kount As Long
Dim strSQL As String

strSQL = "SELECT Surname FROM AddressDetails " _
& "WHERE Surname Is Not Null;"
Set rst = CurrentDb.OpenRecordset(strSQL)

Kount = 0
If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
With rst
Do While Not .EOF
pos = InStr(1, !surname, "(")
If pos > 0 Then
.Edit
!surname = Left(!surname, pos - 1)
.Update
Kount = Kount + 1
End If
.MoveNext
Loop
End With
End If
'clean up
rst.Close
Set rst = Nothing

MsgBox "Changed " & Kount & " Surnames"

'------ END CODE ----------

HTH
 
Top