VBA 修剪访问表中的所有单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12452035/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA to Trim all Cells in an Access Table
提问by JToland
I'm relatively experienced with Object oriented programming, but this is my first time ever working in Office with VBA and I'm entirely stumped by the syntax. I've been doing some searching and messing with it for the past hour or so, but have been trouble actually getting a macro that runs successfully and does what I need.
我在面向对象编程方面比较有经验，但这是我第一次使用 VBA 在 Office 中工作，我完全被语法难住了。在过去一个小时左右的时间里，我一直在进行一些搜索和处理，但实际上很难获得一个成功运行并完成我需要的宏。
I'm attempting to loop through every cell in an Access table and apply the Trim function to the contents of that cell and, as a bonus, I'd like to remove all extra spaces in the string (if any). I.e. " Trim this__string "would simply become "Trim this string"(I used the underscore there to represent individual, multiple spaces since StackOverflow didn't want to show my multiple spaces).
我试图遍历 Access 表中的每个单元格并将 Trim 函数应用于该单元格的内容，作为奖励，我想删除字符串中的所有额外空格（如果有）。即" Trim this__string "会简单地变成"Trim this string"（我在那里使用下划线来表示单个的多个空格，因为 StackOverflow 不想显示我的多个空格）。
Any code example of doing something like this, or at least something to get me close and then I can tinker with it, would be greatly appreciated. Thanks!
任何做这样的事情的代码示例，或者至少让我接近然后我可以修补它的东西，将不胜感激。谢谢！
回答by HansUp
You can remove leading and trailing spaces with the Trim()function in a query.
您可以Trim()使用查询中的函数删除前导和尾随空格。
UPDATE YourTable
SET text_field = Trim(text_field);
If you will be doing this from within an Access session, you could use Replace()to replace a sequence of two spaces with a single space.
如果您将在 Access 会话中执行此操作，您可以使用Replace()一个空格替换两个空格的序列。
UPDATE YourTable
SET text_field = Replace(text_field, ' ', ' ');
However you may need to run that Replace()query more than once to get all the contiguous space characters down to only one.
但是，您可能需要Replace()多次运行该查询才能将所有连续的空格字符缩减为一个。
You could also do a regular expression-based replacement with a user-defined function. I don't know if that's worth the effort, though. And a user-defined function is also only available from within an Access application session.
您还可以使用用户定义的函数进行基于正则表达式的替换。不过，我不知道这是否值得付出努力。用户定义的函数也只能从 Access 应用程序会话中使用。
I overlooked the "every cell in a table" aspect. That makes this more challenging and I don't think you can solve it with a standard macro or query. You can however use VBA code to examine the TableDef, and iterate through its fields ... then call your Trimand/or Replaceoperations on any of those fields whose data type is text or memo.
我忽略了“表格中的每个单元格”方面。这使得这更具挑战性，我认为您无法使用标准宏或查询来解决它。但是，您可以使用 VBA 代码来检查TableDef，并遍历其字段...然后对数据类型为文本或备忘录的任何字段调用您的Trim和/或Replace操作。
Here's a rough code outline to identify which fields of a given table are text type.
这是一个粗略的代码大纲，用于识别给定表的哪些字段是文本类型。
Public Sub FindTextFields(ByVal WhichTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs(WhichTable)
For Each fld In tdf.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
Debug.Print "Do something with " & fld.Name
End If
Next
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub
回答by shruti
Option Compare Database
选项比较数据库
Private Sub Command3_Click()
Call findField(Text1.Value)
End Sub
Public Function findField(p_myFieldName)
Dim db As Database, _
tb As TableDef, _
fd As Field
'''''''''Clearing the contents of the table
DoCmd.RunSQL "delete * from Field_Match_Found"
Set db = CurrentDb
For Each tb In db.TableDefs
For Each fd In tb.Fields
If fd.Name = p_myFieldName Then
strsql = "INSERT INTO Field_Match_Found Values (""" & tb.Name & """, """ & fd.Name & """)"
DoCmd.RunSQL strsql
End If
Next fd
Next tb
Set fd = Nothing
Set tb = Nothing
Set db = Nothing
回答by shruti
If DCount("Account_number", "Field_Match_Found") = 0 Then MsgBox ("No match was found") Else MsgBox ("Check Table Field_Match_Found for your output")
If DCount("Account_number", "Field_Match_Found") = 0 Then MsgBox ("No match was found") Else MsgBox ("Check Table Field_Match_Found for your output")
''''''''''making textbox blank for next time
Text1.Value = ""
End Function
结束函数
