Deletion of certain info within a row???

[dpman39]

Hello Gurus,

I need to perform a massive deletion within Access and I want to find out if there is a simple piece of SQL that can be written to complete this task.

Example:

I have a table called GAB - one of the columns is called email addresses

Currently all the email addresses look like this: m14.dpitts@****.com

I need them to look like this: dpitts@****.com, so all I need to do is remove the 'm14.'

Is there something within Access that will accomplish this?? Kinda like substr in Oracle?

Any help would be great!!!

 

[snoko]

Try this...

Sub ChangeEmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim place As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEmail")

Do Until rst.EOF
place = InStr(1, rst!stremail, ".") + 1 'Find and include dot
rst.Edit
rst!stremail = Mid(rst!stremail, place, Len(rst!stremail)) ' Strip off text upto dot
rst.Update
rst.MoveNext
Loop


End Sub

 

[The_Doc_Man]

You could also build a query that selects records containing ".dpitts@"

If the prefix is constant, look at the RIGHT$ function as a way to UPDATE the email address field.

If the prefix is variable, you also need to use the InStr function to identify the point at which to do the trimming.

In which case your Update query would include an "Update To" row that contains something that looks kind of like...

Right$( [email address], 1 + InStr(1, [email address], "." ) )

I included the "1+" part because if I recall correctly, without it you would get the email address starting with the DOT because of the way Right$ works.