更新字段

76586947

Sub 更新特征字段()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim tableName As String

    ' 打开数据库连接
    Set db = CurrentDb

    ' 第一步: 处理LDPOINT、DXPOINT、GDPOINT、KXPOINT、YXPOINT、LXPOINT、TVPOINT、GBPOINT、EXPOINT
    Dim tables1 As Variant
    tables1 = Array("LDPOINT", "DXPOINT", "GDPOINT", "KXPOINT", "YXPOINT", "LXPOINT", "TVPOINT", "GBPOINT", "EXPOINT")

    For Each tableName In tables1
        strSQL = "UPDATE " & tableName & " SET 特征 = IIf(特征 = '弯头', '探测点', IIf(特征 = '三通', '三分支', IIf(特征 = '四通', '四分支', IIf(特征 IN ('五通', '六通', '七通', '八通', '九通', '十通', '十一通', '十二通'), '多分支', 特征))))"
        db.Execute strSQL
    Next tableName

    ' 第二步: 处理JSPOINT、TQPOINT、WSPOINT、YSPOINT、PGPOINT
    Dim tables2 As Variant
    tables2 = Array("JSPOINT", "TQPOINT", "WSPOINT", "YSPOINT", "PGPOINT")

    For Each tableName In tables2
        strSQL = "UPDATE " & tableName & " SET 特征 = IIf(特征 IN ('六通', '七通', '八通', '九通', '十通', '十一通', '十二通'), '多通', 特征)"
        db.Execute strSQL
    Next tableName

    ' 关闭数据库连接
    db.Close
    Set db = Nothing
End Sub此段代码提示For Each控件变量必须为变体或者对象

rendiule01

Sub 更新特征字段()     Dim db As DAO.Database     Dim rs As DAO.Recordset     Dim strSQL As String     Dim tableName       ' 打开数据库连接     Set db = CurrentDb     ' 第一步: 处理LDPOINT、DXPOINT、GDPOINT、KXPOINT、YXPOINT、LXPOINT、TVPOINT、GBPOINT、EXPOINT     Dim tables1 As Variant     tables1 = Array("LDPOINT", "DXPOINT", "GDPOINT", "KXPOINT", "YXPOINT", "LXPOINT", "TVPOINT", "GBPOINT", "EXPOINT")     For Each tableName In tables1         strSQL = "UPDATE " & tableName & " SET 特征 = IIf(特征 = '弯头', '探测点', IIf(特征 = '三通', '三分支', IIf(特征 = '四通', '四分支', IIf(特征 IN ('五通', '六通', '七通', '八通', '九通', '十通', '十一通', '十二通'), '多分支', 特征))))"         db.Execute strSQL     Next tableName     ' 第二步: 处理JSPOINT、TQPOINT、WSPOINT、YSPOINT、PGPOINT     Dim tables2 As Variant     tables2 = Array("JSPOINT", "TQPOINT", "WSPOINT", "YSPOINT", "PGPOINT")     For Each tableName In tables2         strSQL = "UPDATE " & tableName & " SET 特征 = IIf(特征 IN ('六通', '七通', '八通', '九通', '十通', '十一通', '十二通'), '多通', 特征)"         db.Execute strSQL     Next tableName     ' 关闭数据库连接     db.Close     Set db = Nothing End Sub

补充内容 (2023-12-1 13:53):
Sub 更新特征字段()
    Dim strSQL As String, mySQL As String
    Dim arr(), brr() As Variant
    Dim i As Integer
   
    ' 处理LDPOINT、DXPOINT、GDPOINT、KXPOINT、YXPOINT、LXPOINT、TVPOINT、GBPOINT、EXPOINT
    arr = Array("LDPOINT", "DXPOINT", "GDPOINT", "KXPOINT", "YXPOINT", "LXPOINT", "TVPOINT", "GBPOINT", "EXPOINT")
    For i = LBound(arr) To UBound(arr)
        ' 更新特征为'探测点'的记录
        strSQL = "UPDATE " & arr(i) & " SET 特征 = '探测点' WHERE 特征='弯头';"
        CurrentDb.Execute strSQL
   
        ' 更新特征为非'弯头'的记录
        mySQL = "UPDATE " & arr(i) & " SET 特征 = SWITCH(特征 = '三通', '三分支', " & _
                                                        "特征 = '四通', '四分支', " & _
                                                        "特征 IN ('五通', '六通', '七通', '八通', '九通', '十通', '十一通', '十二通'), '多分支', TRUE, 特征) WHERE 特征 <> '弯头';"
        CurrentDb.Execute mySQL
    Next
   
    ' 处理JSPOINT、TQPOINT、WSPOINT、YSPOINT、PGPOINT
    brr = Array("JSPOINT", "TQPOINT", "WSPOINT", "YSPOINT", "PGPOINT")
    For i = LBound(brr) To UBound(brr)
        ' 更新特征为'六通'以上的记录
        strSQL = "UPDATE " & brr(i) & " SET 特征 = '多通' WHERE 特征 IN ('六通', '七通', '八通', '九通', '十通', '十一通', '十二通');"
        CurrentDb.Execute strSQL
    Next
End Sub