Updating Field (But only part of it)

[homer2002]

Hi

Can anyone shed some light on the best way to solve this problem.

I have a table (lets Call it tblMyTemporaryMadeUpTable)

It has 1 text field (Lets call it MyField)

I also have some data in this field like so

________________________________
MyField
AABAAB
BBABBA
ABACCAB

______________________________

So heres my problem

I need to change all of the letter A's into Letter B's
I had a look at building a query that would change this but i couldnt quite get it.

My original plan is to code this... which is to be fair not a problem, although
if I could crunch this solution into a query then my I.Q may increase by 1 (or double, depending on which way you look at it)

Can anyone enlighten me

thanks Homer

 

[Samoan]

If you are using Access 2000, you should have the replace function.

Using your example, the query would be:

UPDATE tblMyTemporaryMadeUpTable
SET MyField = replace(MyField,"A","B")

 

[homer2002]

Wicked.

Unfortunatly i'm using Access 97 (at work so that aint changing)
Cheers for the info though, filed for future reference

 

[dcx693]

You can indeed code for this, and it's fairly easy to do...but if you've got Excel on the same machine as Access 97, you can call the Excel Substitute function, which provides the same functionality as the Access 2000+ replace function (not to be confused with the Excel Replace function - which does something slightly different).

Place this function in an empty module in your Access database:

Function ExcelSubst(strText As String, strOld As String, _
    strNew As String) As String
Dim obj As Object

    Set obj = CreateObject("Excel.Application")
        ExcelSubst = obj.Substitute(strText, strOld, strNew)
    obj.Quit
    Set obj = Nothing

End Function

Save the module and call it something like "basExcel", just do not call it ExcelSubst (Access will get confused).

You can call the function from a query like this: ExcelSubst([MyField],"A","B").

Be aware that this function might run slowly because each call to it is also an automation call to Excel. Try it out and see if it's acceptable.

 

[homer2002]

ta for the info.

If I were going to code this.

I was thinking more of somthing like this.

I dont know which would be faster, but i like the way i could reference your
code within a query

please excuse the tardy pseudo code
____________________________________________________
Function MyFunction (InField,InTable,Find,Replace)
DIM TempString as string
SET DBS = CurrentDB
SET RST = dbs.OpenRecordset(SELECT Infield from Intable)
rst.MOVEFIRST
DO UNTIL EOF
tempstring = rst.field(0)
for 1 = 1 to Len(TempString)
if Mid(TempString,i,1) = Find then
mid(TempString,i,1) = Replace
end if
rst.MOVENEXT
loop
_____________________________________________________

 

[Cosmos75]

If you are using Access 2000, you should have the replace function.

Using your example, the query would be:

UPDATE tblMyTemporaryMadeUpTable
SET MyField = replace(MyField,"A","B")

Just tried this to replace some dashes in a field with "" but I get an error that says "Undefined Function", is there a reference I need to set? I am using Access 2000.


I am trying to do this in an Update QueryDef, is this supposed to be done in VBA?

 

[Cosmos75]

Couldn't figure it out so I just did it quickly in code.

    Dim myDB As DAO.Database
    Dim myRST As DAO.Recordset

    Set myDB = CurrentDb()
    Set myRST = myDB.OpenRecordset("tblItems")

    myRST.MoveLast
    CountOfItems = myRST.RecordCount
    myRST.MoveFirst

    While Not myRST.EOF
        OrignalItem = myRST("Item")
        If Not IsNull(OrignalItem) Then
            NewItem = [b][color=red]WorksheetFunction[/color][/b].Substitute(OrignalItem, "-", "")
            With myRST
                .Edit
                .Fields("Item") = NewItem
                .Update
            End With
        End If
        myRST.MoveNext
    Wend

    MsgBox "Done!", vbInformation, "Completed Task!"