singo1232001

·PÁ­ìPO ·PÁ¦U¦ì¤j¤j  
³oÃD«Ü¤£¿ù
½m²ß§¹²¦ ªþ¤WÀÉ®×

¶}±Ò"SQL·j´M"¤u§@ªí
´X­Ó²©ö¥\¯à»¡©ú  
1.¥u¦b¦C7¿é¤J ·|¼Ò½k·j¯Á
2.¦C6¦C7³£¿é¤J ·|°Ï¶¡·j¯Á
3.D,E,GÄæ ¦U¬°¤å¦r¼Ò½k·j¯Á ¥iªÅ®æ ¨Ò¦p:A ¥q ¿é¥X A¤½¥q
4.¥þ³¡ªÅ¥Õ ¬°¥þ­¶·j¯Á
5.A~JÄæ ¦P®É¿é¤J ·|and·j¯Á

­­¨î
1.·j´MÀÉ®× »P Àɮרӷ½ ¸ô®|¥Ø«e¨S¦³Àu¤Æ  ¼È©w­n¦b¦P¤@¸ê®Æ§¨©³¤U
2.«Ü¦h¤pbug ¥u¦³°µ¥DÅé´X­Ó¤j¥\¯à ¹L²Óªº«È»s¤Æ¥\¯à»P»Ý¨D ©|¥¼»s§@

Sub ÃöÁä¦r¬d¸ß()
With CreateObject("adodb.connection"): V = Application.Version
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; "
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; "
.Open V & "Data Source=" & ThisWorkbook.Path & "\SearchData.xlsx"

'excel ½Õ¥Îadodb ¥Î sql ®É Äæ¦ì¦WºÙ¦³"."²Å¸¹ ¶·§ï¬° "#"¸¹
If Cells(7, 1) <> "" And Cells(6, 1) = "" Then sq = sq & " and  [No#] like  '%" & Replace(Cells(7, 1), " ", "%") & "%'"
If Cells(7, 1) <> "" And Cells(6, 1) <> "" Then sq = sq & " and  [No#] between " & Cells(6, 1) & " and " & Cells(7, 1) ' & "'"
If Cells(7, 2) <> "" And Cells(6, 2) = "" Then sq = sq & " and  [Inv#]  like '%" & Replace(Cells(7, 2), " ", "%") & "%'"
If Cells(7, 2) <> "" And Cells(6, 2) <> "" Then sq = sq & " and  [Inv#]  between '" & Cells(6, 2) & "' and '" & Cells(7, 2) & "'"
If IsDate(Cells(7, 3)) Then
If Cells(7, 3) <> "" And Cells(6, 3) = "" Then sq = sq & " and  [Date]  like '%" & Cells(7, 3) & "%'"
If Cells(7, 3) <> "" And Cells(6, 3) <> "" Then sq = sq & " and  Format(Date, 'yyyy-MM-dd')  between '" & Format(Cells(6, 3), "yyyy-MM-dd") & "' and '" & Format(Cells(7, 3), "yyyy-MM-dd") & "'"
End If
If Cells(7, 4) <> "" Then sq = sq & " and  [Supplier] like '%" & Replace(Cells(7, 4), " ", "%") & "%'"
If Cells(7, 5) <> "" Then sq = sq & " and  [Inv#(1)] like '%" & Replace(Cells(7, 5), " ", "%") & "%'"
If Cells(7, 6) <> "" And Cells(6, 6) = "" Then sq = sq & " and  [Part No#] like '%" & Replace(Cells(7, 6), " ", "%") & "%'"
If Cells(7, 6) <> "" And Cells(6, 6) <> "" Then sq = sq & " and  [Part No#]  between '" & Cells(6, 6) & "' and '" & Cells(7, 6) & "'"
If Cells(7, 7) <> "" Then sq = sq & " and  [Prod# Name] like '%" & Replace(Cells(7, 7), " ", "%") & "%'"
If Cells(7, 8) <> "" And Cells(6, 8) = "" Then sq = sq & " and  [Qty]  like '%" & Replace(Cells(7, 8), " ", "%") & "%'"
If Cells(7, 8) <> "" And Cells(6, 8) <> "" Then sq = sq & " and  [Qty]  between " & Cells(6, 8) & " and " & Cells(7, 8)
If Cells(7, 9) <> "" And Cells(6, 9) = "" Then sq = sq & " and  [Amt#]  like '%" & Replace(Cells(7, 9), " ", "%") & "%'"
If Cells(7, 9) <> "" And Cells(6, 9) <> "" Then sq = sq & " and  [Amt#]  between " & Cells(6, 9) & " and " & Cells(7, 9)
If Cells(7, 10) <> "" And Cells(6, 10) = "" Then sq = sq & " and  [Total]  like '%" & Replace(Cells(7, 10), " ", "%") & "%'"
If Cells(7, 10) <> "" And Cells(6, 10) <> "" Then sq = sq & " and  [Total]  between " & Cells(6, 10) & " and " & Cells(7, 10)

If sq <> "" Then sq = Mid(sq, 5, 99999)
If sq <> "" Then sq = "select * from [Data$A1:J] where " & sq
If sq = "" Then sq = "select * from [Data$A1:J] "
Sheets("SQL·j´M").Cells(9, 1).Resize(10000, 10).ClearContents
Sheets("SQL·j´M").Cells(9, 1).CopyFromRecordset .Execute(sq)
.Close: End With
End Sub

Sub ²M°£ÃöÁä¦r()
Sheets("SQL·j´M").Range("a6:J7").ClearContents
End Sub