使用VBA,我怎么可以搜索文本字符串,例如CHIR,在一个名为表ServiceYES,在该领域的服务
Using VBA, how can I search for a text string, for example "CHIR", in a table called "ServiceYES", in the field "Service".
在这之后,我想救场周边所有的行CHIR的表存在ServicesYES。在ServiceYES表如下:
After that, I would like to save the neighboring field for all the rows that "CHIR" exists in the table "ServicesYES". The "ServiceYES" table is below:
我基本上,想找到所有的CHIR在服务一栏,然后保存这些都对CHIR的左侧,如FRANKL_L,SANTIA_D作为一个数组。
I basically, want to find all the "CHIR" in "Service" column and then save the names which are on the left of the CHIR, eg "FRANKL_L", "SANTIA_D" as an array.
感谢提前你的所有帮助。
Thanks for all your help in advance.
推荐答案
通过创建一个 SELECT
查询开始。
Start by creating a SELECT
query.
SELECT Code_Perso
FROM ServicesYES
WHERE Service = 'CHIR';
使用 SELECT DISTINCT code_Perso
如果你只想要唯一值。
Use SELECT DISTINCT Code_Perso
if you want only the unique values.
添加 ORDER BY code_Perso
如果你愿意让他们按字母顺序排序。
Add ORDER BY Code_Perso
if you care to have them sorted alphabetically.
一旦你有了一个令人满意的查询,通过打开基于该查询DAO记录,并循环 code_Perso
值返回。
Once you have a satisfactory query, open a DAO recordset based on that query, and loop through the Code_Perso
values it returns.
您不需要直接加载到您的最后一个数组。它可能更容易把它们添加到一个逗号分隔的字符串。之后,你可以使用斯普利特()
函数(假设你有Access版本> = 2000)来创建数组。
You don't need to load them directly into your final array. It might be easier to add them to a comma-separated string. Afterward you can use the Split()
function (assuming you have Access version >= 2000) to create your array.
下面的示例code,让你开始。这主要是标准的锅炉板,但它可能实际工作......一旦你给它yourquery。
Here's sample code to get you started. It's mostly standard boiler-plate, but it might actually work ... once you give it "yourquery".
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strItems As String
Dim varItems As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("yourquery", dbOpenSnapshot)
With rs
Do While Not .EOF
strItems = strItems & "," & !Code_Perso
.MoveNext
Loop
.Close
End With
If Len(strItems) > 0 Then
' discard leading comma '
strItems = Mid(strItems, 2)
varItems = Split(strItems, ",")
Else
MsgBox "Oops. No matching rows found."
End If
Set rs = Nothing
Set db = Nothing
相关推荐
最新文章