Question want to replace "/" with "" in a table "Master" (1 Viewer)

lokanathas · Aug 14, 2017

Simple update query is working, but every day i need to do this activity for 10 fields 4 tables every day.

arnelgp · Aug 14, 2017

paste this public function in a module:

Public Function fnFixSlashOnTable()
Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim db As DAO.Database
Dim strSQL As String

On Error GoTo err_handler
Set db = CurrentDb
' recurse to all tables in this database
For Each td In db.TableDefs
If Not (td.Name Like "MSys*") Then
'if not system table, process it
strSQL = ""
For Each fld In td.Fields
'recurse to all fields in this table
If fld.Type = dbText Or fld.Type = dbMemo Then
'if field is Text Or Memo, include it for replacement of "/" with ""
'build our update query string here.
strSQL = strSQL & _
"[" & fld.Name & "]=Iif(Not IsNull([" & fld.Name & "]),Replace([" & fld.Name & "],'/',''),Null),"
End If
Next
If strSQL <> "" Then
'remove extra "," from our query string
strSQL = Left(strSQL, Len(strSQL) - 1)
'add additional text
strSQL = "Update [" & td.Name & "] Set " & strSQL & ";"

'execute the update
'replace "/" with ""
CurrentDb.Execute strSQL, dbFailOnError
End If
End If
Next td

exit_gracefully:
'remove all objects we use
If Not (fld Is Nothing) Then Set fld = Nothing
If Not (td Is Nothing) Then Set td = Nothing
If Not (db Is Nothing) Then Set db = Nothing
Exit Function

err_handler:
MsgBox Err.Number & ": " & Err.Description
Resume exit_gracefully
End Function

then create a Macro (say mcrFixSlash) that will call this function:

RunCode
Function Name: fnFixSlashOnTable()

everytime you run this macro it will remove "/" from All your tables that has Text Or Memo fields.

lokanathas · Aug 14, 2017

arnelgp said:
paste this public function in a module:

then create a Macro (say mcrFixSlash) that will call this function:

everytime you run this macro it will remove "/" from All your tables that has Text Or Memo fields.

This VBA code working.. Thank u..

lokanathas · Aug 23, 2017

arnelgp said:
paste this public function in a module:

then create a Macro (say mcrFixSlash) that will call this function:

everytime you run this macro it will remove "/" from All your tables that has Text Or Memo fields.

If i want to run this code in specific table, where i need to change in the given code by you

arnelgp · Aug 23, 2017

Code:

Public Function fnFixSlashOnTable(Optional ByVal strTableName As String)
    Dim td As DAO.TableDef
    Dim fld As DAO.Field
    Dim db As DAO.Database
    Dim strSQL As String
    Dim strWildCard As String

On Error GoTo err_handler
    Set db = CurrentDb
    
    strWildCard = IIf(strTableName = "", "*", strTableName)
    ' recurse to all tables in this database
    For Each td In db.TableDefs
        If Not (td.Name Like "MSys*") Then
            strSQL = ""
            If td.Name Like strWildCard Then
                For Each fld In td.Fields
                'recurse to all fields in this table
                    If fld.type = dbText Or fld.type = dbMemo Then
                    'if field is Text Or Memo, include it for replacement of "/" with ""
                    'build our update query string here.
                        strSQL = strSQL & _
                            "[" & fld.Name & "]=Iif(Not IsNull([" & fld.Name & "]),Replace([" & fld.Name & "],'/',''),Null),"
                    End If
                Next
                If strSQL <> "" Then
                'remove extra "," from our query string
                    strSQL = Left(strSQL, Len(strSQL) - 1)
                    'add additional text
                    strSQL = "Update [" & td.Name & "] Set " & strSQL & ";"
        
                    'execute the update
                    'replace "/" with ""
                    CurrentDb.Execute strSQL, dbFailOnError
                End If
            End If
        End If
    Next td
    
exit_gracefully:
    'remove all objects we use
    If Not (fld Is Nothing) Then Set fld = Nothing
    If Not (td Is Nothing) Then Set td = Nothing
    If Not (db Is Nothing) Then Set db = Nothing
    Exit Function
    
err_handler:
    MsgBox err.Number & ": " & err.description
    Resume exit_gracefully
End Function

to use for specific table:

fnfnFixSlashOnTable "yourTableName"

otherwise it will run on all tables.

Question want to replace "/" with "" in a table "Master" (1 Viewer)

lokanathas

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

lokanathas

Registered User.

lokanathas

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread