Hi Guys
I wrote a function (see below) that I use to clean up a text field in a query and strip away unwanted syntax. If I'd like to apply this function to multiple fields in a table/query, how best to do this (without having to reference every text field in a table/query)?
Thanks
******************************************************
Public Function TrmChar(ReplaceChar As String) As String
Dim Originals As Variant, Replacements As Variant
Dim i As Long
Originals = Array(Chr(169), Chr(191), Chr(218), Chr(63), Chr(47), Chr(59), "")
Replacements = Array("", "", "", "", "", "", "")
TrmChar = ReplaceChar
For i = 0 To 6
TrmChar = Replace(TrmChar, Originals(i), Replacements(i), Compare:=vbTextCompare)
Next
End Function
Applying a Function to an entire table
-
- StarLounger
- Posts: 55
- Joined: 21 Sep 2011, 06:59
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Applying a Function to an entire table
In an update query, you must include every field that you want to update explicitly, you can't use * to specify all fields.
If you have lots of fields to be updated, or if you have to generate such queries frequently, you could use VBA to generate the update query:
Example of use:
GenerateUpdateQuery "tblEmployees"
If you have lots of fields to be updated, or if you have to generate such queries frequently, you could use VBA to generate the update query:
Code: Select all
Sub GenerateUpdateQuery(TableName As String)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim strQueryName As String
Dim strSQL As String
'On Error GoTo ErrHandler
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(TableName)
For Each fld In tdf.Fields
If fld.Type = dbText Then
strSQL = strSQL & ", [" & fld.Name & "]=" & _
"Replace(Replace(Replace(Replace(Replace(Replace(" & _
"[" & fld.Name & "],Chr(169),"""")," & _
"[" & fld.Name & "],Chr(191),"""")," & _
"[" & fld.Name & "],Chr(218),"""")," & _
"[" & fld.Name & "],Chr(63),"""")," & _
"[" & fld.Name & "],Chr(47),"""")," & _
"[" & fld.Name & "],Chr(59),"""")"
End If
Next fld
If strSQL = "" Then
MsgBox "Nothing to update!", vbInformation
GoTo ExitHandler
Else
strSQL = "UPDATE [" & TableName & "] SET" & _
Mid(strSQL, 2)
strQueryName = "qryUpdate_" & TableName
On Error Resume Next
Set qdf = dbs.QueryDefs(strQueryName)
On Error GoTo ErrHandler
On Error GoTo 0
If qdf Is Nothing Then
Set qdf = dbs.CreateQueryDef(strQueryName, strSQL)
dbs.QueryDefs.Refresh
Else
qdf.Sql = strSQL
End If
End If
ExitHandler:
Set fld = Nothing
Set qdf = Nothing
Set tdf = Nothing
Set dbs = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
GenerateUpdateQuery "tblEmployees"
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 55
- Joined: 21 Sep 2011, 06:59
Re: Applying a Function to an entire table
Thanks Hans
Will put to good use
Will put to good use