VB实现从SQL导出数据到EXCEL无数据！
DTPicker1用于选择日期，程序如下：
With Adodc1
.ConnectionString = "dsn=jczx "
.UserName = "jczx2 "
.Password = " "
.RecordSource = "select 铅品号, 批号,生产单位, 标准编号,生产日期,化验日期,化验员,判定,银Ag,铜Cu,锑Sb,锡Sn,砷As,铋Bi,铁Fe,锌Zn,总和,铅Pb,审核人,批量 from djqsr where 生产日期 between ( " & Year(DTPicker1.Value) & "/ " & Month(DTPicker1.Value) & "/ " & Day(DTPicker1.Value) & ") and ( " & Year(DTPicker2.Value) & "/ " & Month(DTPicker2.Value) & "/ " & Day(DTPicker2.Value) & ") order by 生产日期 desc, 批号 "
.Refresh
End With
Set oexcel = CreateObject( "Excel.Application ")
Set obook = oexcel.Workbooks.Add
Set osheet = obook.Worksheets(1)
osheet.range( "A1 ").Value = "铅品号 "
osheet.range( "B1 ").Value = "批号 "
osheet.range( "C1 ").Value = "生产单位 "
osheet.range( "D1 ").Value = "标准编号 "
osheet.range( "E1 ").Value = "生产日期 "
osheet.range( "F1 ").Value = "化验日期 "
osheet.range( "G1 ").Value = "化验员 "
osheet.range( "H1 ").Value = "判定 "
osheet.range( "I1 ").Value = "银Ag "
osheet.range( "J1 ").Value = "铜Cu "
osheet.range( "K1 ").Value = "锑Sb "
osheet.range( "L1 ").Value = "锡Sn "
osheet.range( "M1 ").Value = "砷As "
osheet.range( "N1 ").Value = "铋Bi "
osheet.range( "O1 ").Value = "铁Fe "
osheet.range( "P1 ").Value = "锌Zn "
osheet.range( "Q1 ").Value = "总和 "
osheet.range( "R1 ").Value = "铅Pb "
osheet.range( "S1 ").Value = "审核人 "
osheet.range( "T1 ").Value = "批量 "
osheet.range( "A2 ").CopyFromRecordset Adodc1.Recordset
obook.SaveAs "d:\report\电解铅 从 " & DTPicker1.Value & " 到 " & DTPicker2.Value & ".xls "
oexcel.Quit
Set osheet = Nothing
Set obook = Nothing
Set oexcel = Nothing
MsgBox "电解铅报表生成成功 "
程序运行后，每列有列名。但是列名下面没数据！
请大侠们赐教呀！
多谢谢了！
[解决办法]
好象是select 语句有点问题，DTpicker的用法有问题，这句改改：
.RecordSource = "select 铅品号, 批号,生产单位, 标准编号,生产日期,化验日期,化验员,判定,银Ag,铜Cu,锑Sb,锡Sn,砷As,铋Bi,铁Fe,锌Zn,总和,铅Pb,审核人,批量 from djqsr where 生产日期 between ' " & DTPicker1.Value & " ' and ' " & Year(DTPicker2.Value) & " '
order by 生产日期 desc, 批号 "
[解决办法]
要格式化日期的话可以这样写：
.RecordSource = "select 铅品号, 批号,生产单位, 标准编号,生产日期,化验日期,化验员,判定,银Ag,铜Cu,锑Sb,锡Sn,砷As,铋Bi,铁Fe,锌Zn,总和,铅Pb,审核人,批量 from djqsr where 生产日期 between " & Format(DTPicker1.Value, "YYYY/MM/DD ") & " and " & format(DTPicker2.Value, "YYYY/MM/DD ") & "
order by 生产日期 desc, 批号 "
[解决办法]
楼上说的对，我也这么想的
关键是造出一个类似 "...between '2007-04-02 ' and '2007-04-02 ' "的字符串来。
where 生产日期 between ( " & Year(DTPicker1.Value) & "/ " & Month(DTPicker1.Value) & "/ " & Day(DTPicker1.Value) & ") and ( " & Year(DTPicker2.Value) & "/ " & Month(DTPicker2.Value) & "/ " & Day(DTPicker2.Value) & ") order by 生产日期 desc, 批号 "
改为
"where 生产日期 between ( ' " & Year(DTPicker1.Value) & "/ " & Month(DTPicker1.Value) & "/ " & Day(DTPicker1.Value) & " ') and ( ' " & Year(DTPicker2.Value) & "/ " & Month(DTPicker2.Value) & "/ " & Day(DTPicker2.Value) & " ') order by 生产日期 desc, 批号 "
试试
