Lọc dữ liệu-chứa nhiều điều kiện lọc?

Liên hệ QC
Em không biết, máy em chạy bình thường mà, chỉ có 4 dòng thôi. File bài 96.
@NHN_Phương lỗi này do MS quy định thế. Bạn đọc thêm https://docs.microsoft.com/en-us/of...esktop-database-reference/filter-property-ado, nếu có nhiều điều kiện lọc thì làm kiểu này rất dài. Còn sau LIKE ở đây nên dùng dấu nháy đơn.
Xin chào @Hau151978
Cảm ơn Bạn nhiều, với máy của OT khi sử dụng đọa code "ABC_Hau151978" cũng ra kết quả như hình ảnh bài #96.
Nếu dữ liệu chuẩn OT nghĩ sử dụng các câu lệnh truy vấn là chuẩn và chính xác nhất.
----------
Với link Bạn gửi OT đã để các điều kiện trong dấu nháy đơn như vậy:
Mã:
    SrtSQL = " ([Customer Name] LIKE '*Julie*' OR [Customer Name] LIKE '*aren*')" & _
        " AND ([Product category] LIKE '*Office*')" & _
        " AND ([Order date] >=#1/Jan/2011# And [Order date] <=#31/Dec/2011#)" & _
        " AND Profit>0"
Nhưng code vẫn bị lỗi, không biết có phải khai báo liệt kê thêm không ạ, link Bạn gửi tiếng Anh về chuyên môn lĩnh vực code OT đọc cũng không thể hiểu được ạ ahihi.
Nếu Bạn xử lý được rồi làm phiền Bạn gửi cho OT 1 chuỗi truy vấn dạng trên OK với ạ.
 
Mình vẫn thích sử dụng dạng code nào mình có thể kiểm soát để tùy biến khi cần. Bạn tham khảo code trong file sẽ có lúc áp dụng được.
Hãy để ý sự linh động của việc hoán chuyển giữa cột 1 và cột 8. Bạn có thể lấy hoặc không lấy bất kỳ cột nào

Bài này thuộc dạng thà sửa giày chứ không gọt chân
 

File đính kèm

  • Orders-With Nulls.xlsm
    321 KB · Đọc: 18
Mình vẫn thích sử dụng dạng code nào mình có thể kiểm soát để tùy biến khi cần. Bạn tham khảo code trong file sẽ có lúc áp dụng được.
Hãy để ý sự linh động của việc hoán chuyển giữa cột 1 và cột 8. Bạn có thể lấy hoặc không lấy bất kỳ cột nào

Bài này thuộc dạng thà sửa giày chứ không gọt chân

Xin cảm ơn anh @Quang_Hải đã chỉ dẫn thêm giải pháp dùng "AdvancedFilter" ạ.
Kính chúc anh ngày mới niềm vui và sức khỏe ạ.
 
----------
Với link Bạn gửi OT đã để các điều kiện trong dấu nháy đơn như vậy:
Mã:
    SrtSQL = " ([Customer Name] LIKE '*Julie*' OR [Customer Name] LIKE '*aren*')" & _
        " AND ([Product category] LIKE '*Office*')" & _
        " AND ([Order date] >=#1/Jan/2011# And [Order date] <=#31/Dec/2011#)" & _
        " AND Profit>0"
Nhưng code vẫn bị lỗi...

Câu lệnh dùng trong rs.Filter này nó không chịu cái vụ gộp mấy cái "OR" vô cùng một nhóm rồi mới "AND" mà phải tách ra từng nhóm "AND" rồi mới "OR".
Nói chung câu lệnh sẽ rất dài dòng:
Mã:
SrtSQL = " ([Customer Name] LIKE '*Julie*'" & _
        " AND ([Product category] LIKE '*Office*')" & _
        " AND ([Order date] >=#1/Jan/2011# And [Order date] <=#31/Dec/2011#)" & _
        " AND Profit>0 " & _
        " OR [Customer Name] LIKE '*aren*')" & _
        " AND ([Product category] LIKE '*Office*')" & _
        " AND ([Order date] >=#1/Jan/2011# And [Order date] <=#31/Dec/2011#)" & _
        " AND Profit>0 "

** Bổ sung: phải trả về Recordset ban đầu rồi mới bấm Filter tiếp.

With Rst
.Open sDULIEU, cn, adOpenKeyset
.Filter = ""
.Filter = SrtSQL
....


Theo tôi, tốt nhất trong trường hợp này là dùng mệnh đề WHERE trong câu lệnh SQL luôn cho đơn giản vừa giảm được lượng Recordset lấy về.
 
Lần chỉnh sửa cuối:
Câu lệnh dùng trong rs.Filter này nó không chịu cái vụ gộp mấy cái "OR" vô cùng một nhóm rồi mới "AND" mà phải tách ra từng nhóm "AND" rồi mới "OR".
Nói chung câu lệnh sẽ rất dài dòng:
Mã:
SrtSQL = " ([Customer Name] LIKE '*Julie*'" & _
        " AND ([Product category] LIKE '*Office*')" & _
        " AND ([Order date] >=#1/Jan/2011# And [Order date] <=#31/Dec/2011#)" & _
        " AND Profit>0 " & _
        " OR [Customer Name] LIKE '*aren*')" & _
        " AND ([Product category] LIKE '*Office*')" & _
        " AND ([Order date] >=#1/Jan/2011# And [Order date] <=#31/Dec/2011#)" & _
        " AND Profit>0 "

Theo tôi, tốt nhất trong trường hợp này là dùng mệnh đề WHERE trong câu lệnh SQL luôn cho đơn giản vừa giảm được lượng Recordset lấy về.
Xin chào anh @ongke0711
Cảm ơn anh đã chỉ dẫn ạ.
Úi xời, như vậy mỗi lần thêm 1 điều kiện là phải x với số lần thêm,,nếu như vậy đúng là dài thât, nếu muốn dùng chắc phải viết hàm tự tạo xử lý hoặc sửa hàm 'ConvertCriteria' để xử lý vấn đề này.
Nhưng mà sao câu truy vấn trên OT copy về chạy thử mà vẫn bị lỗi anh nhỉ, anh có thể đính kèm file cho OT được không ạ.
 
Nhưng mà sao câu truy vấn trên OT copy về chạy thử mà vẫn bị lỗi anh nhỉ, anh có thể đính kèm file cho OT được không ạ.

** Bổ sung: phải trả về Recordset ban đầu rồi mới bấm Filter tiếp.

With Rst
.Open sDULIEU, cn, adOpenKeyset
.Filter = ""
.Filter = SrtSQL
....
 

File đính kèm

  • Orders-With Nulls(filter multiple values).xlsm
    691.5 KB · Đọc: 6
Hic vẫn chưa được anh ạ, nút "Filter_Rst" vẫn bị lỗi .

Đã sửa.
Chú ý dấu ngoặc trong câu lệnh SQL.
Mã:
SrtSQL = "([Customer Name] LIKE '*Julie*'" & _
                " AND [Product category] LIKE '*Office*'" & _
                " AND [Order date] >=#1/Jan/2011# And [Order date] <=#31/Dec/2011#" & _
                " AND [Profit] > 0)" & _
                " OR ([Customer Name] LIKE '*aren*'" & _
                " AND [Product category] LIKE '*Office*'" & _
                " AND [Order date] >=#1/Jan/2011# And [Order date] <=#31/Dec/2011#" & _
                " AND [Profit]>0)"

*** Anh có sai trong vụ "thiết lập rs.Filter ="" để trả lại Recordset ban đầu" anh nói ở trên. Trong code này thì không cần thiết vì mỗi lần bấm nút [Filter] là tạo một Connection, một Recordset mới hoàn toàn chứ không dùng lại Recordset cũ đã tải về (trong bộ nhớ) nên không cần xoá Filter cũ.
Để giảm tải băng thông thì chỉ cần tải toàn bộ Recordset về một lần rồi, mỗi lần bấm Filter, thì xoá Filter cũ, tạo câu lệnh Filter mới rồi chạy thôi.
 

File đính kèm

  • Orders-With Nulls(filter multiple values).xlsm
    678.8 KB · Đọc: 9
Lần chỉnh sửa cuối:
Đã sửa.
Chú ý dấu ngoặc trong câu lệnh SQL.
Mã:
SrtSQL = "([Customer Name] LIKE '*Julie*'" & _
                " AND [Product category] LIKE '*Office*'" & _
                " AND [Order date] >=#1/Jan/2011# And [Order date] <=#31/Dec/2011#" & _
                " AND [Profit] > 0)" & _
                " OR ([Customer Name] LIKE '*aren*'" & _
                " AND [Product category] LIKE '*Office*'" & _
                " AND [Order date] >=#1/Jan/2011# And [Order date] <=#31/Dec/2011#" & _
                " AND [Profit]>0)"
Oài, câu lệnh này không bị lỗi nữa rồi cảm ơn anh @ongke0711 nhiều ạ, hic hic...
 
Nhưng code vẫn bị lỗi, không biết có phải khai báo liệt kê thêm không ạ, link Bạn gửi tiếng Anh về chuyên môn lĩnh vực code OT đọc cũng không thể hiểu được ạ ahihi.
Nếu Bạn xử lý được rồi làm phiền Bạn gửi cho OT 1 chuỗi truy vấn dạng trên OK với ạ.
Chuỗi filter thì bạn OngKe đã viết và giải thích rồi, mình chỉ giải thích thêm thôi. Ví dụ ta mong muốn điều kiện lọc là (A or B) and C thì phải viết là (A and C) or (B and C). Chú ý các mệnh đề A, B, C phải có dạng FieldName_Operator_Value ví dụ Profit > 0 hay [Customer Name] like '*Ja*'. Nếu lọc Customer name lấy tất cả các tên và Profit>0 thì phải ghi là [Cutstomer Name] like '*' and Profit>0 hoặc bỏ name chỉ còn Profit>0, nếu viết thành TRUE and Profit>0 sẽ bị lỗi.
 
Chuỗi filter thì bạn OngKe đã viết và giải thích rồi, mình chỉ giải thích thêm thôi. Ví dụ ta mong muốn điều kiện lọc là (A or B) and C thì phải viết là (A and C) or (B and C). Chú ý các mệnh đề A, B, C phải có dạng FieldName_Operator_Value ví dụ Profit > 0 hay [Customer Name] like '*Ja*'. Nếu lọc Customer name lấy tất cả các tên và Profit>0 thì phải ghi là [Cutstomer Name] like '*' and Profit>0 hoặc bỏ name chỉ còn Profit>0, nếu viết thành TRUE and Profit>0 sẽ bị lỗi.
Xin chào bạn @Hau151978,
Cảm ơn Bạn đã giải thích thêm, nhờ bạn giải thích thêm mà OT hiểu rõ hơn về cú pháp của nó rồi ạ.
Bạn có thể viết cho OT cái hàm 'ConvertCriteria' sử dụng cho trường hợp Recordset này giống như trường hợp dùng Connection được không ạ.
OT thấy hàm này tư duy cao , sử dụng rất hiệu quả & viết câu truy vấn đơn giản ạ.
Một lần nữa cảm ơn Bạn.
 
Nếu dùng code cho mệnh đề WHERE như bài #45 của bạn @Hau151978 thì tôi có sửa đổi một chút để tránh các lỗi nếu để trống ô các điều kiện lọc dữ liệu, và lọc [Profit] số âm (<-1000).
Cách viết này nó dài hơn chút nhưng tôi nghĩ nó đơn giản, dễ hiểu, dễ thêm điều kiện... :)
(sử dụng lại hầu hết code của bạn @Hau151978 nhé)

Mã:
Sub ABC()
    Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim sLike As String
    Dim s As String, s1 As String, s2 As String, s3 As String, s4 As String, s5 As String
    
    s = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 XML;HDR=YES"";"
    cn.Open s
    sLike = "*"
    s = "SELECT * FROM [Orders$] WHERE 1=1"
    s1 = " AND " & ConvertCriteria("Customer Name", Sheets("Filter").Range("B1"))
    s2 = " AND " & ConvertCriteria("Product category", Sheets("Filter").Range("B2"))
    s3 = " AND [Order date] >=#" & Sheets("Filter").Range("D1") & "#"
    s4 = " AND [Order date] <=#" & Sheets("Filter").Range("D2") & "#"
    If Not IsNumeric(Trim(Sheets("Filter").Range("E2"))) Then
        s5 = " AND [Profit] " & Sheets("Filter").Range("E2")
    Else
        s5 = " AND [Profit] = " & Sheets("Filter").Range("E2")
    End If
    
    If Sheets("Filter").Range("B1") <> "" Then
        s = s & s1
    End If
    If Sheets("Filter").Range("B2") <> "" Then
        s = s & s2
    End If
    If Sheets("Filter").Range("D1") <> "" Then
        s = s & s3
    End If
    If Sheets("Filter").Range("D2") <> "" Then
        s = s & s4
    End If
    If Sheets("Filter").Range("E2") <> "" Then
        s = s & s5
    End If
    'Debug.Print s
    
    rs.Open s, cn
    Sheets("Filter").Range("A4:J1000000").ClearContents
    Sheets("Filter").Range("A4").CopyFromRecordset rs
    rs.Close
    cn.Close
End Sub
 

File đính kèm

  • Orders-With Nulls(filter multiple values).xlsm
    741.9 KB · Đọc: 19
Nếu dùng code cho mệnh đề WHERE như bài #45 của bạn @Hau151978 thì tôi có sửa đổi một chút để tránh các lỗi nếu để trống ô các điều kiện lọc dữ liệu, và lọc [Profit] số âm (<-1000).
Cách viết này nó dài hơn chút nhưng tôi nghĩ nó đơn giản, dễ hiểu, dễ thêm điều kiện... :)
(sử dụng lại hầu hết code của bạn @Hau151978 nhé)

Mã:
Sub ABC()
    Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim sLike As String
    Dim s As String, s1 As String, s2 As String, s3 As String, s4 As String, s5 As String
   
    s = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 XML;HDR=YES"";"
    cn.Open s
    sLike = "*"
    s = "SELECT * FROM [Orders$] WHERE 1=1"
    s1 = " AND " & ConvertCriteria("Customer Name", Sheets("Filter").Range("B1"))
    s2 = " AND " & ConvertCriteria("Product category", Sheets("Filter").Range("B2"))
    s3 = " AND [Order date] >=#" & Sheets("Filter").Range("D1") & "#"
    s4 = " AND [Order date] <=#" & Sheets("Filter").Range("D2") & "#"
    If Not IsNumeric(Trim(Sheets("Filter").Range("E2"))) Then
        s5 = " AND [Profit] " & Sheets("Filter").Range("E2")
    Else
        s5 = " AND [Profit] = " & Sheets("Filter").Range("E2")
    End If
   
    If Sheets("Filter").Range("B1") <> "" Then
        s = s & s1
    End If
    If Sheets("Filter").Range("B2") <> "" Then
        s = s & s2
    End If
    If Sheets("Filter").Range("D1") <> "" Then
        s = s & s3
    End If
    If Sheets("Filter").Range("D2") <> "" Then
        s = s & s4
    End If
    If Sheets("Filter").Range("E2") <> "" Then
        s = s & s5
    End If
    'Debug.Print s
   
    rs.Open s, cn
    Sheets("Filter").Range("A4:J1000000").ClearContents
    Sheets("Filter").Range("A4").CopyFromRecordset rs
    rs.Close
    cn.Close
End Sub

Xin cảm ơn anh @ongke0711,
OT chắc chắn là có ứng dụng phương pháp này cho những vấn đề của OT rồi, vì đây cũng là ngôn ngữ SQL quen thuộc mà OT vẫn hay dùng.
Tuy nhiên OT muốn hoàn thiện thêm phương pháp sử dụng Recordset thôi ạ.
OT cảm ơn anh rất rất nhiều ạ,kính chúc anh sức khỏe & thành công trong năm 2021 tới này.
 
Xin chào bạn @Hau151978,
Cảm ơn Bạn đã giải thích thêm, nhờ bạn giải thích thêm mà OT hiểu rõ hơn về cú pháp của nó rồi ạ.
...
OT thấy hàm này tư duy cao , sử dụng rất hiệu quả & viết câu truy vấn đơn giản ạ.
Tư duy cao là do mình chứ lị. Thử tư duy các trường hợp:
-B1 là chuỗi 3 cus nối lại, B2 là 2 product nối lại
- B1 là chuỗi 2 cus nối lại, B2 là 3 product nối lại
- B1 là chuỗi m cus nối lại, B2 là n product nối lại, trong đó m và n bất kỳ và có thể = 0
Viết tổng quát thì sẽ viết thế nào?
 
Xin chào bạn @Hau151978,
Cảm ơn Bạn đã giải thích thêm, nhờ bạn giải thích thêm mà OT hiểu rõ hơn về cú pháp của nó rồi ạ.
Bạn có thể viết cho OT cái hàm 'ConvertCriteria' sử dụng cho trường hợp Recordset này giống như trường hợp dùng Connection được không ạ.
OT thấy hàm này tư duy cao , sử dụng rất hiệu quả & viết câu truy vấn đơn giản ạ.
Một lần nữa cảm ơn Bạn.
Mã:
Sub abcd()
    Const Col1 As String = "[Customer Name]"
    Const Col2 As String = "[Product Category]"
    Dim s1 As String, s2 As String, s3 As String, s As String
    Dim a, b, c, i, j, k
    s1 = "a;b;c"
    s2 = "x;y"
    s3 = " and Profit>0"
    
    If s1 = "" Then s1 = "*"
    If s2 = "" Then s2 = "*"
    a = Split(s1, ";")
    b = Split(s2, ";")
    
    ReDim c(1 To (UBound(a) + 1) * (UBound(b) + 1))
    For i = 0 To UBound(a)
        For j = 0 To UBound(b)
            k = k + 1
            c(k) = Col1 & " like '*" & a(i) & "*' and " & Col2 & " like '*" & b(j)
        Next
    Next
    s = "(" & Join(c, "*' " & s3 & ") or (") & "*' " & s3 & ")"

End Sub
Code mình đã xét trường hợp s1 = "" hoặc *, s2="" hoặc *; s3 là chuỗi bắt đầu bởi " AND " và các điều kiện còn lại (ví dụ s3=" and Profit>0". Nếu s1=s2="" thì chuỗi có dạng
([Customer Name] like '***' and [Product Category] like '***' and Profit>0) nếu không chạy được thì bạn bỏ cặp ngoặc ngoài cùng đi. Nói chung, đây chỉ là vấn đề xử lý chuỗi thôi.
 
Nói chung, đây chỉ là vấn đề xử lý chuỗi thôi.
Tôi cố tình không làm, chỉ gợi ý cho tư duy xem thế nào, bạn lại làm trước mất tiêu. Cái tư duy quan trọng ở đây là với B1 = m, B2 = n thì sẽ có bao nhiêu cái And trong ngoặc và Or với nhau. Rồi với bấy nhiêu cái thì dùng cách nào lôi ra hết
 
Mã:
Sub abcd()
    Const Col1 As String = "[Customer Name]"
    Const Col2 As String = "[Product Category]"
    Dim s1 As String, s2 As String, s3 As String, s As String
    Dim a, b, c, i, j, k
    s1 = "a;b;c"
    s2 = "x;y"
    s3 = " and Profit>0"
   
    If s1 = "" Then s1 = "*"
    If s2 = "" Then s2 = "*"
    a = Split(s1, ";")
    b = Split(s2, ";")
   
    ReDim c(1 To (UBound(a) + 1) * (UBound(b) + 1))
    For i = 0 To UBound(a)
        For j = 0 To UBound(b)
            k = k + 1
            c(k) = Col1 & " like '*" & a(i) & "*' and " & Col2 & " like '*" & b(j)
        Next
    Next
    s = "(" & Join(c, "*' " & s3 & ") or (") & "*' " & s3 & ")"

End Sub
Code mình đã xét trường hợp s1 = "" hoặc *, s2="" hoặc *; s3 là chuỗi bắt đầu bởi " AND " và các điều kiện còn lại (ví dụ s3=" and Profit>0". Nếu s1=s2="" thì chuỗi có dạng
([Customer Name] like '***' and [Product Category] like '***' and Profit>0) nếu không chạy được thì bạn bỏ cặp ngoặc ngoài cùng đi. Nói chung, đây chỉ là vấn đề xử lý chuỗi thôi.
Tôi cố tình không làm, chỉ gợi ý cho tư duy xem thế nào, bạn lại làm trước mất tiêu. Cái tư duy quan trọng ở đây là với B1 = m, B2 = n thì sẽ có bao nhiêu cái And trong ngoặc và Or với nhau. Rồi với bấy nhiêu cái thì dùng cách nào lôi ra hết


1609769368799.png

OT đã 'hiểu xương xương' vấn đề , xin cảm ơn Bạn @Hau151978 và chú Mỹ cự kỳ nhiều. :yahoo:
 
OT đã 'hiểu xương xương' vấn đề , xin cảm ơn Bạn @Hau151978 và chú Mỹ cự kỳ nhiều. :yahoo:
Quan trọng là không dựa vào code có sẵn để liệt kê ra như vậy.
Tư duy là lấy giấy viết ra liệt kê hết các trường hợp, đếm, cho trường hợp 3 cus và 2 product. Làm lần nữa với 2 cus và 3 product, hoặc 3 cus 3 product và cũng đếm. Sẽ kết luận được là m x n trường hợp. Dùng m x n để Redim mảng kết quả
Sau đó nhìn cái tờ giấy đó tìm ra quy luật để tìm code lôi được hết ra, cụ thể là 2 vòng lặp for lồng nhau.

Phương pháp là vậy, chứ có người làm cho riết rồi ỷ lại
 
Quan trọng là không dựa vào code có sẵn để liệt kê ra như vậy.
Tư duy là lấy giấy viết ra liệt kê hết các trường hợp, đếm, cho trường hợp 3 cus và 2 product. Làm lần nữa với 2 cus và 3 product, hoặc 3 cus 3 product và cũng đếm. Sẽ kết luận được là m x n trường hợp. Dùng m x n để Redim mảng kết quả
Sau đó nhìn cái tờ giấy đó tìm ra quy luật để tìm code lôi được hết ra, cụ thể là 2 vòng lặp for lồng nhau.

Phương pháp là vậy, chứ có người làm cho riết rồi ỷ lại
Vâng chú Mỹ con sẽ cố gắng,
Tiện con cũng thông tin với chú rằng khi con bấm trả lời bài này thôi, thì chỉ còn 7 bài nữa thôi là chú sẽ về Nhì nhé.. hehehe :yahoo:

1609773748762.png
 
Tiện con cũng thông tin với chú rằng khi con bấm trả lời bài này thôi, thì chỉ còn 7 bài nữa thôi là chú sẽ về Nhì nhé.. hehehe :yahoo:
Tôi nói rồi, có mấy loại rỗi hơi, tôi không phải loại rỗi hơi để hành người khác, mà là rỗi hơi nhưng không chịu bị hành :yahoo: mà là ... à thôi, qua đọc các món ăn Bắc xưa đi
 
Web KT
Back
Top Bottom