怎么对数据进行排序, 初学者求助

lsdongjh

1. Public Function check_demand(product As String, workingDate As Date) As Boolean
   
2.     Dim Conn As Object, Rst As Object, strPath As String
   
3.     Dim strConn As String, strSQL As String
   
4.     Dim lngCount As Long
   
5.     Dim blIsFind As Boolean
   
6.     Dim demand_date As Date
   
7.    
   
8.     Set Conn = CreateObject("ADODB.Connection")
   
9.     Set Rst = CreateObject("ADODB.Recordset")
   
10.     strPath = ThisWorkbook.FullName '设置工作簿的完整路径和名称
    
11.     Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接
    
12.         Case Is <= 11
    
13.             strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & strPath
    
14.         Case Is >= 12
    
15.             strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
    
16.     End Select
    
17. 
    
18.     Conn.Open strConn '打开数据库链接
    
19. 
    
20.     '''''''''''''''''''''''''''''''''
    
21.     strSQL = "SELECT Max([Delivery Date]) As [最后日期] " & _
    
22.               "FROM [Demand$] " & _
    
23.               "WHERE module_type = '17HLLeiCHNG'"
    
24. 
    
25.     Rst.Open strSQL, Conn, 3, 1 '执行查询，并将结果输出到记录集对象
    
26. 
    
27.     lngCount = Rst.RecordCount
    
28.     If lngCount = 0 Then
    
29.         blIsFind = False
    
30.     Else
    
31.         demand_date = Rst("最后日期").Value & ""
    
32.         If demand_date = workingDate Then
    
33.             blIsFind = True
    
34.         Else
    
35.             blIsFind = False
    
36.         End If
    
37.     End If
    
38.    
    
39.     Set Rst = Nothing
    
40.     Set Conn = Nothing
    
41.    
    
42.     check_demand = blIsFind
    
43.    
    
44. End Function

复制代码

zopey

'check if have demand after workingdate  字段e首行 加个下划线 delivery_date

Public Function check_demand(product As String, workingDate As Date) As Boolean
check_demand = False

Set cnn = CreateObject("adodb.connection")
cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName

Sql2 = "select max(delivery_date) as lastdate from [Demand$c:e] where module_type='" & product & "'"
    Set rst2 = CreateObject("adodb.Recordset")
    rst2.Open Sql2, cnn, 1, 1

    Do While Not rst2.EOF
       If rst2("lastdate") >= workingDate Then check_demand = True
    rst2.MoveNext
    Loop

cnn.Close
Set cnn = Nothing

End Function

jokerzhh

lsdongjh 发表于 2018-6-26 16:25

谢谢大佬, 我研究下

jokerzhh

zopey 发表于 2018-6-26 16:46
'check if have demand after workingdate  字段e首行 加个下划线 delivery_date

Public Function check ...

谢谢, 我看下

魂断蓝桥

jokerzhh 发表于 2018-6-26 16:08
T.T  demand是预计排产表, 现在有很多产品想要生成, 在排出实际生产计划前, 先要检查这款产品在计划表里 ...

我这英文实在是不灵光，你看是不是这个意思

按照module_type字段取Delivery Date字段的最大值？

代码如下：

Sub 查询()
Dim cnn As Object
Dim Sql As String
Set cnn = CreateObject("ADODB.CONNECTION")
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName
Sql = "select [module_type],max([Delivery Date]) from [Demand$a1:e]  group by [module_type] "
[g2].CopyFromRecordset cnn.Execute(Sql)
cnn.Close: Set cnn = Nothing
End Sub

返回结果如下：

这个是最简单的应用，还可以返回数组等等，看你最后的需求。

17HLLeiCHNG	2018-7-9
17HLLeiSING	2018-7-9
17HLLuiCHNS	2018-7-9
17HLLuiKORS	2018-7-9
17HLLuiSINM	2018-7-30
17HLUsxCHNFa	2018-7-16
17HLUsxCHNFb	2018-7-16
17HLUsxCHNFc	2018-7-16
17HLUsxCHNFd	2018-7-16
17HLUsxCHNFe	2018-7-16
17HLUsxCHNFf	2018-7-16
17HLUsxCHNFg	2018-7-16
17HLUsxCHNFn	2018-7-16

jokerzhh

魂断蓝桥 发表于 2018-6-27 13:35
我这英文实在是不灵光，你看是不是这个意思

按照module_type字段取Delivery Date字段的最大值？

谢谢, 麻烦了