如何从SQL到VBA文本框中检索多个数据

Question

我有一个这样的代码：

SQL = "SELECT STA_ID, AVG(STA_INCOME) " & _
"FROM STAFF " & _
"WHERE STAFF_EXP = '" & Label1.Value & "' " & _
"GROUP BY STA_ID"
rs.Open SQL, cn
With rs
i = 0
Do Until .EOF
label2.Value = rs(1)
label3.Value = rs(0)
i = i + 1
.MoveNext
Loop
End With

我可以在标签2中检索平均STA_INCOME，但我只能在标签3中检索一个而不是多个STU_ID数据。如果文本框(标签3(与我键入的Label1.Value匹配，我该怎么做才能让它显示多个值？非常感谢。

Answer 1

考虑连接记录集循环中的所有ID和平均值以填充文本框：

' RETRIEVING SINGLE AVERAGE
avg_SQL = "SELECT AVG(STA_INCOME) " & _
"FROM STAFF " & _
"WHERE STAFF_EXP = '" & Label1.Value & "' "
rs.Open avg_SQL, cn
label2.Value = rs.Fields(0).Value
rs.Close

' RETRIEVING MULTIPLE IDs
id_SQL = "SELECT STA_ID" & _
"FROM STAFF " & _
"WHERE STAFF_EXP = '" & Label1.Value & "' " & _
"GROUP BY STA_ID"
rs.Open SQL, cn
' ITERATIVELY CONCATENATE VALUES
str_ids = ""
Do Until rs.EOF
str_ids = str_ids & rs.Fields(0).Value & vbNewLine
rs.MoveNext
Loop
label3.Value = str_ids
rs.Close