如何搜索字段的表中的访问 [英] How to search a field in a table in Access

问题描述

使用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

这篇关于如何搜索字段的表中的访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！