Function Summary, possible? (1 Viewer)

[mattkorguk]

Hi All,
I've put the following Function together to clear out unwanted bits before exporting as csv. I was just wondering if there's a way of holding the table names together with a total count of each item replaced? I wasn't sure if these were stored and could be returned?!
Suggestions welcomed.
Cheers
Matt

Public Function ClearCommas()
On Error GoTo ClearErr
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    Dim strSQL2 As String
    Dim strSQL3 As String
    
Set db = CurrentDb
    
    For Each tb In db.TableDefs
    
        If tb.Name = "Client" Or _
            tb.Name = "Dependants" Or _
            tb.Name = "Plan" Or _
            tb.Name = "Tasks" _
            Then
            
            For Each fld In tb.Fields
DoCmd.SetWarnings False
                strSQL = "UPDATE [" & tb.Name & "] SET [" & fld.Name & "] = Replace([" & fld.Name & "],',','');"
                strSQL2 = "UPDATE [" & tb.Name & "] SET [" & fld.Name & "] = Replace([" & fld.Name & "],Chr(10),' ');"
                strSQL3 = "UPDATE [" & tb.Name & "] SET [" & fld.Name & "] = Replace([" & fld.Name & "],Chr(13),'');"
                
                DoCmd.RunSQL strSQL
                DoCmd.RunSQL strSQL2
                DoCmd.RunSQL strSQL3
                
                DoEvents
            Next fld
        End If
DoCmd.SetWarnings True
    Next tb
Exit Function
ClearErr:
    If Err.Number = 3709 Or Err.Number = 3113 Or Err.Number = 3073 Then
        Resume Next
    Else
        MsgBox Err.Number & " / " & Err.Description
        Resume Next
    End If
End Function

 

[pbaldy]

I use a database variable, the Execute method and RecordsAffected:

db.Execute "qryAppendTrans", dbFailOnError
lngBookings = db.RecordsAffected

Though in your case, with no WHERE clause I suspect you'll find that the count is the total in the table, not the total changed.

 

[plog]

To build on pbaldy's comment--giving your queries a WHERE clause will be simple--just limit it to records that contain the characters you are replacing. For example the WHERE clause of strSQL would be:

"WHERE ([" &fld.Name & "] LIKE '*,*')"

 

[mattkorguk]

Ahh, ok thanks for this, so would I need to set them up as qry defs rather than sql as above?
Matt

 

[pbaldy]

To use RecordsAffected? No, the execute method works with SQL too.