老铁们急需一段查询代码，请老铁们不吝赐教

代码小菜鸟

查询数据库内容的代码.zip (335.23 KB, 下载次数: 42)

2023-10-4 09:29 上传

点击文件名下载附件

如图需求，根据左边的条件，可单一个条件查询，也可以多条件查询这样子。请老铁们给段代码，谢谢
数据库因为文件大小关系没有放内容，只是做了个格式，老铁们可以自行根据列名创建些测试数据。

uuiu

1. Sub try()
   
2. Dim conn As Object, sql As String, arr, sr As String
   
3. Set conn = CreateObject("adodb.connection")
   
4. arr = Sheets("处理明细查询").Range("b2:i3")
   
5. 
   
6. If arr(2, 1) And arr(2, 2) Then sr = Replace(arr(1, 1), "(从)", "") & " >=#" & arr(2, 1) & "# And " & Replace(arr(1, 2), "(到)", "") & " <=#" & arr(2, 2) & "# or "
   
7. If arr(2, 1) And arr(2, 2) = "" Then sr = sr & Replace(arr(1, 1), "(从)", "") & " >=#" & arr(2, 1) & "# or "
   
8. If arr(2, 2) And arr(2, 1) = "" Then sr = sr & Replace(arr(1, 2), "(到)", "") & " <=#" & arr(2, 2) & "# or "
   
9. If Len(arr(2, 3)) Then sr = sr & arr(1, 3) & " = '" & arr(2, 3) & "' or "
   
10. If Len(arr(2, 4)) Then sr = sr & arr(1, 4) & " = '" & arr(2, 4) & "' or "
    
11. If Len(arr(2, 5)) Then sr = sr & arr(1, 5) & " = '" & arr(2, 5) & "' or "
    
12. If Len(arr(2, 6)) Then sr = sr & arr(1, 6) & " = '" & arr(2, 6) & "' or "
    
13. If arr(2, 7) And arr(2, 8) Then sr = sr & Replace(arr(1, 7), "(从)", "") & " >=#" & arr(2, 7) & "# And " & Replace(arr(1, 8), "(到)", "") & " <=#" & arr(2, 8) & "#"
    
14. If arr(2, 7) And arr(2, 8) = "" Then sr = sr & Replace(arr(1, 7), "(从)", "") & " >=#" & arr(2, 7) & "# or "
    
15. If arr(2, 8) And arr(2, 7) = "" Then sr = sr & Replace(arr(1, 8), "(到)", "") & " <=#" & arr(2, 8) & "#"
    
16. If InStr(sr, "or") Then sr = Replace(sr, " or ", "")
    
17. If sr = "" Then
    
18. MsgBox "请输入查询明细"
    
19. Exit Sub
    
20. End If
    
21. 
    
22. sql = "select * from 处理页 where " & sr
    
23. conn.Open "provider=Microsoft.jet.OLEDB.4.0;data source=" & ThisWorkbook.Path & "\订单登记表.mdb"
    
24. Sheets("处理明细查询").UsedRange.Offset(7, 0).ClearContents
    
25. Sheets("处理明细查询").Range("a8").CopyFromRecordset conn.Execute(sql)
    
26. conn.Close
    
27. Set conn = Nothing
    
28. End Sub

复制代码

我也就学了sql几天，简单试了一下可以用，试试吧，你这个做得我迷迷糊糊

代码小菜鸟

uuiu 发表于 2023-10-7 18:19
我也就学了sql几天，简单试了一下可以用，试试吧，你这个做得我迷迷糊糊

感谢老铁的，倾囊相授，测试了一下貌似按照设备号搜索是没问题的，但是其他条件，比如订单日期几号到几号或者到货日期几号到几号 都不能搜索，包括双条件的。