请教一个关于left/right函数格式的问题，谢谢！！

Ombre

Private Sub cmdCalculate_Click()
    Dim strSQL1, strsql2 As String
   
   
    If IsNull(Me.cboTableName) Then Exit Sub
   
     strSQL1 = " UPDATE " & Me.cboTableName & " SET " & _
             " 划分 = Switch([积压时间] Like '*1-10*','D1-D2',[积压时间] Like '*11-20*','D2-D3',[积压时间] Like '*21-30*','D3-D4')," & _
             " 奖金 = Switch([积压时间] Like '*1-10*',[单价]*0.2,[积压时间] Like '*11-20*',[单价]*0.3,[积压时间] Like '*21-30*',[单价]*0.4) where " & "Left([代码], 1) =1"
    strsql2 = " UPDATE " & Me.cboTableName & " SET " & _
             " 划分 = Switch([积压时间] Like '*1-10*','D1-D2',[积压时间] Like '*11-20*','D2-D3',[积压时间] Like '*21-30*','D3-D4')," & _
             " 奖金 = Switch([积压时间] Like '*1-10*',[单价]*0.2,[积压时间] Like '*11-20*',[单价]*0.3,[积压时间] Like '*21-30*',[单价]*0.4) where " & "Left([代码], 1) =2"

   
    Debug.Print strSQL1
   
  Debug.Print strsql2

    CurrentDb.Execute strSQL1
        CurrentDb.Execute strsql2
    Me.fsubQuery.Requery
   
    Me.txtSumOfPrice1 = DSum("[单价]", Me.cboTableName, "[积压时间] Like '*1-10*'")
    Me.txtSumOfPrice2 = DSum("[单价]", Me.cboTableName, "[积压时间] Like '*11-20*'")
    Me.txtSumOfPrice3 = DSum("[单价]", Me.cboTableName, "[积压时间] Like '*21-30*'")
    Me.txtSumOfBonus1 = DSum("[奖金]", Me.cboTableName, "[积压时间] Like '*1-10*'")
    Me.txtSumOfBonus2 = DSum("[奖金]", Me.cboTableName, "[积压时间] Like '*11-20*'")
    Me.txtSumOfBonus3 = DSum("[奖金]", Me.cboTableName, "[积压时间] Like '*21-30*'")

End Sub

Ombre

用if应该就麻烦了
只能用ADO取数据来循环了

Ombre

dim rs as adodb.recordset
set rs= new adodb.recordset
sql="select * from " & Me.cboTableName
rs.open sql,currentproject.connection.connectionstring,1,1
do until rs.eof
if left(rs(0),1)=1 then
docmd.runsql "update......."
elseif left(rs(0),1)=2
docmd.runsql "update........"
endif
loop
以上没验证

Ombre

因为是select *
rs(0) 就是说第一个字段 rs(1)就是第2个字段...也可以用rs("字段名")来表示
两个1常量 分别表示adOpenDynamic,adLockBatchOptimistic

Ombre

我看你的附件不是已经解决了吗?