This is the macro that I am running but it takes a long time to execute.....not sure if it is still running or gone into a hang. Please help!

The table has 9,00,000 rows.

Public Sub LoginAppend()
Dim ds As Database
Dim rs As Recordset
Dim i As Integer
Dim strLogin As String

On Error Resume Next
Set ds = CurrentDb
Set rs = ds.OpenRecordset("select * from Service_t")

Do Until rs.EOF
strLogin = ""
For i = 1 To Len(rs!login)
If Mid(rs!login, i, 1) = "#" Then
Exit For
End If

strLogin = strLogin & Mid(rs!login, i, 1)
Next
rs.Edit
rs!loginRevised = strLogin
rs.Update
rs.MoveNext
Loop
End Sub

http://downloads.btabdevelopment.com/screenshots/welcometoawf.png

What is this actually supposed to be doing and why are you doing it to all 9,000,000 rows?

It checks each character for '# in a column in all the records and extracts the string into a string upto the '#'. If the column does not contain a '#', it extracts the entire field for that record.

Thanks.

well something like

function getsubstrg(strg as string) as string
dim pos as long
pos= instr(1,strg,"#")
if pos>0 then
getsubstrg = left(strg,pos-1)
else
getsubstrg = strg
end if
end function

Seems to me that an update query is in order here. It would be much faster.

bobs right

in a query add the table and pull down the columns you want

then have another column called

fieldpart: getsubstrg(nz(targetfield,""))

put my function in a module

now your query will include the substrings,

It needs the nz, in case the textfield is null

Actually the entire code is in a Module. Please send me the updated code.

Thanks.

Actually the entire code is in a Module. Please send me the updated code.

Thanks.
The point is that both of us have made is Do NOT use a module. USE AN UPDATE QUERY.