根據年月日自動編號

廠Function AutoBh()
     Dim rs As New ADODB.Recordset
    Dim strSql As String
    Dim temp As String
    temp = Format(GetServerDate, "yyyymm")
    strSql = "select 編號 from test_a  where 編號 like '" & temp & "%' order by 編號 desc"
    If rs.State = adStateOpen Then rs.Close
    With rs
        .Source = strSql
        .Open , conn, adOpenStatic, adLockReadOnly, 1
    End With
    If rs.EOF Then
      AutoBh = temp & "0001"
    Else
       AutoBh = Format(Val(rs!編號) + 1, "0000000000")
    End If
End Function

Function GetServerDate()

    If rs.State = adStateOpen Then rs.Close
    With rs
        .Source = "select getdate()"
        .Open , conn, adOpenStatic, adLockReadOnly, 1
    End With
   
GetServerDate = Format(rs.Fields(0).Value, "yyyy/mm/dd")

End Function