如何在 Access 数据库中创建一个以“A1,A2..... H12”格式显示记录索引的字段？

Question

如何创建一个字段，自动将记录中的索引值转换为单元格编号，如图所示？

我有 96 个单元格，以 A1 开头，以 H12 结尾，如下所示：

Answer 1

您可以创建一个 VBA 函数，然后可以在查询、表单、报告或 VBA 中调用该函数：

Public Function JoinFromSql(DataSource As String, Optional Delimiter As String = ",") As String
    Dim db As DAO.Database, rs As DAO.Recordset, s As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset(DataSource, dbOpenForwardOnly)
    Do Until rs.EOF
        If s = "" Then
            s = Nz(rs(0))
        Else
            s = s & Delimiter & Nz(rs(0))
        End If
        rs.MoveNext
    Loop
    rs.Close: Set rs = Nothing
    db.Close: Set db = Nothing
    JoinFromRecordset = s
End Function

你可以这样称呼它：

JoinFromSql("SELECT [Cell number] FROM Table1 ORDER BY [Index]")

上述函数使用DAO。如果您愿意，ADODB可以编写类似的函数：

Public Function JoinFromSql(DataSource As String, Optional Delimiter As String = ",") As String
    Dim cnn As ADODB.Connection, rs As ADODB.Recordset, s As String

    Set cnn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open DataSourceName, cnn, adOpenForwardOnly, adLockReadOnly
    Do Until rs.EOF
        If s = "" Then
            s = Nz(rs(0))
        Else
            s = s & Delimiter & Nz(rs(0))
        End If
        rs.MoveNext
    Loop
    rs.Close: Set rs = Nothing
    cnn.Close: Set cnn = Nothing
End Function