MS Access查找和在长文本字段中突出显示与任何表列列表值匹配的多个子字符串 [英] MS Access Find & Highlight Multiple Substrings that Match Any Table Column List Value in Long Text Field

问题描述

我是一个尝试制定策略的初学者.

I'm a beginner trying to figure out a strategy.

样本数据 表:成分 字段:公式成分 字段内容(加长文本):大头菜，韭菜，胡萝卜，小麦，面粉，黄油，糖，鸡蛋，牛奶，花生酱，碎玉米粉，全麦燕麦，白菜，姜黄，丁香，香料，天然牛肉风味，碎牛肉.

Sample Data Table: Ingredients Field: FormulaIngredients Field Contents (Long Text Rich Text): rutabaga, leeks, carrots, wheat, flour, butter, sugar, eggs, milk, peanut butter, stone ground corn meal, whole grain oats, cabbage, turmeric, cloves, spice, natural beef flavor, ground beef.

表:受限制的表 具有100个要比较的值的列:RestrictedItem 示例列值: 牛奶 烤 勺子 萝卜 芥末 蒸汽

Table: RestrictedTable Column with 100 values to compare: RestrictedItem Example Column Values: milk bake spoon carrots mustard steam

所需结果:想要突出显示/更改FormulaIngredients字段中与表列中任何〜100值匹配的匹配字体:RestrictedItem.想象一下斜体字是红色的.

Desired Result: Want to highlight/change font of matches within FormulaIngredients field that match to any of the ~100 values in a Table Column: RestrictedItem. Imagine the italicized words are red.

大头菜，韭菜，胡萝卜，小麦，面粉，黄油，糖，鸡蛋，牛奶，花生酱，碎玉米粉，全谷物燕麦，白菜，姜黄，丁香，香料，天然牛肉风味，碎牛肉，芥末酱.

rutabaga, leeks, carrots, wheat, flour, butter, sugar, eggs, milk, peanut butter, stone ground corn meal, whole grain oats, cabbage, turmeric, cloves, spice, natural beef flavor, ground beef, mustard.

或者，复制并复制将FormulaIngredients的内容替换为新字段，该字段将红色应用于与表列:RestrictedItem"匹配的那些单词.

Or, copy & replace the content of FormulaIngredients to a new field which applies a red color to those words that match to Table Column: RestrictedItem.

我已经探索过...

InStr 问题 :我不想将信息字符串传递给表单/报表，也不想在乎找到子字符串的位置，无论如何在任何情况下都想找到所有的子字符串&.可以的.

InStr Problem: I don't want to pass a string of info to a form/report, also do not care about the position the substring was found in, I want to find all of them, in any case & duplicates are ok.

Dim strTemp, strTempEnd As String
Dim strSearch As String

strSearch = Me.OpenArgs

If InStr(1, Me.Text0, strSearch) <> 0 Then
    strTemp = Left(Me.Text0, InStr(1, Me.Text0, strSearch) - 1)
    strTempEnd = Mid(Me.Text0, Len(strTemp) + Len(strSearch) + 1)
    strTemp = strTemp & "<font color=red>" & strSearch & "</font>"
    strTemp = strTemp & strTempEnd

    Me.Text0 = strTemp
End If

HTML 问题:此解决方案比较了两列数据.我想将一个字段与值表进行比较在一个长文本"字段中查找多个匹配项.

HTML Problem: This solution compares 2 columns of data. I want to compare one field to a table of values & find multiple matches within that one Long Text field.

    Public Function MyCompare(c1t As Variant, c2t As Variant)

  Dim strResult    As String
  Dim s        As String
  Dim i        As Integer
  Dim bolSame     As Boolean

  If IsNull(c1t) Or IsNull(c2t) Then Exit Function

  bolSame = True

  For i = 1 To Len(c2t)
   s = Mid(c2t, i, 1)
   If Mid(c1t, i, 1) = s Then
    If bolSame = False Then
      bolSame = True
      s = "</strong></font>" & s
     End If
   Else
     If bolSame = True Then
      bolSame = False
      s = "<font color=red><strong>" & s
     End If
   End If
   strResult = strResult & s
  Next

  If bolSame = False Then
   strResult = strResult & "</strong></font>"
  End If
  MyCompare = strResult

End Function

VBA 问题:我必须在我的长文本"表单字段&中输入要比较/搜索的表中的所有100个关键字. REPLACE是区分大小写的搜索.有没有一种方法可以与值表进行比较?

VBA Problem: I would have to type out all 100 keywords in my table that I want to compare/search in my Long Text form field & REPLACE is a case sensitive search. Is there a way to compare to a table of values?

Me.{ControlName}.Value = Replace(Me.{ControlName}.Value _
                               , "red", "<font color=red>red</font>")

推荐答案

建议的VBA过程:

1. 打开RestrictedTable的记录集

1. opens recordset of RestrictedTable

遍历记录集并在成分表"上运行UPDATE操作SQL

loops through recordset and runs an UPDATE action SQL on Ingredients table

示例:

Dim db As DAO.Database  
Dim rs As DAO.Recordset  
Set db = CurrentDb  
Set rs = db.OpenRecordset("SELECT * FROM RestrictedTable")  
Do While Not rs.EOF  
    db.Execute "UPDATE Ingredients SET FormulaIngredients = Replace([FormulaIngredients], '" & rs!RestrictedItem & "', '<font color=red>" & rs!RestrictedItem & "</font>')"  
    rs.MoveNext  
Loop

我使用静态参数进行了快速测试，并且可以正常工作.

I did a quick test with static parameter and it worked.

CurrentDb.Execute "UPDATE Ingredients SET FormulaIngredients = Replace([FormulaIngedients], 'carrots', '<font color=red>carrots</font>')"

公式文本是否具有CARROTS，carrots，CArRoTs都无关紧要-全部都将匹配并替换为carrots.但是，如果公式文本中包含carrot，则该文本将不匹配.

Doesn't matter if the formula text has CARROTS, carrots, CArRoTs - all will match and be replaced with carrots. However, if formula text has carrot, it will not match.

可能不需要担心部分匹配，但是如果要确保仅更改整个单词，请在搜索前后使用空格并替换字符串:' carrots '.除非您要突出显示buttermilk中的milk.

Partial matches might not be a concern, but if you want to be sure to change only whole words, use space before and after the search and replace strings: ' carrots '. Unless you want milk in buttermilk to get highlighted.

如果您不想更改原始数据，请首先复制到另一个字段，然后在UPDATE语句中使用该另一个字段.这可以通过Do While块之前的UPDATE语句来完成.

If you don't want to alter the original data, first copy to another field then use that other field in the UPDATE statement. This can be done with an UPDATE statement before the Do While block.

db.Execute "UPDATE Ingredients SET FormulaHighlight=FormulaIngredients"

这篇关于MS Access查找和在长文本字段中突出显示与任何表列列表值匹配的多个子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！