专家坐堂-用 Fill 从后台数据库模糊查询问题Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

e.Form.Controls("Button6").Enabled = False

Dim time1 As Date = Date.Now

\'查询起始日期

Dim ssdate As WinForm.DateTimePicker = e.Form.Controls("DateTimePicker1")

Dim eedate As WinForm.DateTimePicker = e.Form.Controls("DateTimePicker2")

Dim sdate As Date = ssdate.Value

Dim edate As Date = eedate.Value

\'查询条件，模糊查询

Dim filter As String

Filter = "申请日期 >= \'" & sdate & "\' And 申请日期 <= \'" & edate & "\'"

Dim sts() As String = {"申请人","申请单号","物料名称","规格图号","工程号","建议厂家","类别","品牌"}

For Each st As String In sts

Dim val As String = e.Form.Controls(st).value

If val > "" Then

val = val.Replace("*","[*]")

If filter = "" Then

filter = st & " like \'*" & val & "*\'"

Else

filter = filter & " And " & st & " like \'*" & val & "*\'"

End If

\'用 fill 填充数据

Tables("最全查询_Table1").Fill("Sel ect prsub.[_Identify], prsub.申请日期, prsub.品牌,prsub.建议厂家, prsub.申请人, prsub.申请单号, prsub.物料名称, prsub.规格图号, prsub.单位,prsub.工程号,prsub.站号,prsub.类别, prsub.数量 as 申请数量, prsub.备注, prsub.核准, prsub.PR子表流水号, b.订单数量, c.入库数量,d.领用数量, (prsub.数量 - isnull(b.订单数量 , 0)) As 未订数量 from {prsub} left join (S elect PR子表流水号, sum(数量) As 订单数量 from {posub} group by PR子表流水号) As b on prsub.PR子表流水号 = b.PR子表流水号 left join (S elect PR子表流水号, sum(入库数量) As 入库数量 from {inssub} group by PR子表流水号) As c on prsub.PR子表流水号 = c.PR子表流水号 left join (Sel ect PR子表流水号, sum(领用数量) As 领用数量 from {lysub} group by PR子表流水号) As d on prsub.PR子表流水号 = d.PR子表流水号 where " & Filter,"wsdcsql",True)

Tables("最全查询_Table1").SetColVisibleWidth("申请人|50|申请单号|60|申请日期|80|物料名称|180|规格图号|180|申请数量|60|订单数量|70|入库数量|70|领用数量|70|单位|40|工程号|60|站号|60|类别|60|备注|100|PR子表流水号|80|品牌|80|建议厂家|100")

e.Form.Controls("Button6").Enabled = True

Dim time2 As Date = Date.Now

Dim t3 As TimeSpan = time2 - time1

MessageBox.Show("本次查询耗时" & t3.Minutes & "分" & t3.Seconds & "秒" & t3.Milliseconds & "毫秒")