- Sub 查询库存() '参考:Access联合查询.xls
- Dim cnn As Object, SQL$, s1$, s2$
- Dim StrSQL As String
- Dim rst As New ADODB.Recordset
- Set cnn = CreateObject("ADODB.connection")
- cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\到货记录.mdb"
- SQL = "select 入库单号,货物名称规格,计划日期,到货日期,iif(not 到货日期 is null,DateDiff(""d"",到货日期,计划日期),DateDiff(""d"",date(),计划日期)) as 准时或延误 from 明细表 where not 计划日期 is null"
- 'SQL = "select 入库单号,货物名称规格,计划日期,到货日期,到货日期-计划日期 as 准时或延误 from 明细表"
- Range("a1") = SQL
- rst.Open SQL, cnn, adOpenKeyset
- Range("B3:G65536,D4:D5").ClearContents
- Range("b9").Resize(, 5) = Array("单号", "货物规格", "计划日期", "到货日期", "准时或延误") '
- Range("b10").CopyFromRecordset cnn.Execute(SQL)
- rst.Close
- Set rst = Nothing
- Set cnn = Nothing
- MsgBox "查询完毕!", , "提示"
- End Sub
复制代码 |