Code a button to FIND & REPLACE? 1

[Spyder757]

Is it doable to code a button to FIND & REPLACE?

When I import data to my database it had a few peices of data I need to switch.

I'd like to FIND XXX and REPLACE WITH YYYY.

Can thise function be assigned to a buttom to check the table and do this? And if so could someone show me how?

Thanks.

Spyder757

 

[BillPower]

This will open the Find and Replace window, place it in the On Click event of a button:

DoCmd.RunCommand acCmdReplace

Is this what you meant, if not please elaborate on what you want to do. With data examples, field names, table names, form names, control names etc.

 

[Spyder757]

Ok. When I import my data every morning the "USER ID" field has DXX infront of each number.

I want to find and replace DXX with nothing at the push of the button rather than having to key it into the FIND and REPLACE window.

Spyder757

 

[BillPower]

Make a Backup of Your Database.

Copy and Paste this into a Global Module:

Function RemoveMyDXX(strString As String)
Dim strChar As String
Dim intLen As Integer
Dim intCharPos As Integer
strChar = "DXX"
intCharPos = InStr(1, strString, strChar)
If intCharPos > 0 Then
intLen = Len(strString)
strString = Mid(strString, intCharPos + 3, (intLen - (intCharPos - 3)))
End If
RemoveMyDXX = strString
End Function

Create a new query and paste the following into the SQL section, replace MyTable with your own table name:

UPDATE MyTable SET MyTable.[USER ID] = RemoveMyDXX([User ID]);

Run the query after your import each day.