我有一个我无法回答的问题,我的问题是我有一个宏,该宏正在使用SQL查找来访问SQL数据库并提取信息。在excel中,它总是将日期强制为DD / MM / YYYY(您可以强制使用格式,但是当格式传递给SQL时,即使外观看起来正确,它仍会以DD / MM / YYYY的形式出现)。
我已经尝试对代码进行许多更改以尝试“强制”执行,但是我没有幸免于使其复杂化,还是应该做起来更困难?哈哈。
我将提供我的VB代码和“属性”,以显示通过使用宏建立的“连接”。
注意,格式必须为YYYY-MM-DD,因为这是它在数据库中的存储方式。当前解决该问题的唯一方法是在日期前加一个''',例如'2013-12-01来强制使用它,否则它将执行到2013年1月12日。
有任何想法吗?我已经动脑了太久了,哈哈。
问候杰米
如果需要该信息,则服务器为SQLEXPRESS服务器。
代码如下:
Sub CustomisedSQLQuery()
'
' SQL Query to allow user customisation.
'
'
Dim FileName As String
Dim User As String
Dim StartDate As String
Dim EndDate As String
Dim Category As String
Dim Confirm As Variant
Confirm = MsgBox("Have you made sure that at least one of the search criteria's is populated? If so your excel may crash or you may kill the database.", vbQuestion + vbYesNo, "Wait....")
If Confirm = vbNo Then ActiveWorkbook.Sheets("Input Sheet").Activate
If Confirm = vbNo Then Exit Sub
FileName = Worksheets("Input Sheet").Cells(10, 1)
User = Worksheets("Master DATA").Cells(1, 1)
StartDate = Worksheets("Input Sheet").Cells(10, 3)
EndDate = Worksheets("Input Sheet").Cells(10, 4)
Category = Worksheets("Master DATA").Cells(1, 5)
MyStr = Format(StartDate, "yyyy/mm/dd")
MyStr = Format(EndDate, "yyyy/mm/dd")
Sheets("Output Sheet").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DRIVER=SQL Server;SERVER=SERVERADDRESS;UID=USERNAME;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=ID" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT DocumentsRead.userID, DocumentsRead.fileName, DocumentsRead.category, DocumentsRead.downloadedByUser, DocumentsRead.timeDownloaded, DocumentsRead.timeR" _
, _
"ead" & Chr(13) & "" & Chr(10) & "FROM EndUsers.dbo.DocumentsRead DocumentsRead" & Chr(13) & "" & Chr(10) & "WHERE (DocumentsRead.fileName Like '" & FileName & "') AND (DocumentsRead.category='" & Category & "') AND (DocumentsRead.timeRead Is Null) " _
, "AND (DocumentsRead.timeDownloaded Between {ts '" & StartDate & " 00:00:01'} An" _
, "d {ts '" & EndDate & " 00:00:01'})")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("Input Sheet").Select
End Sub
这是来自SQL的连接属性
SELECT DocumentsRead.userID, DocumentsRead.fileName, DocumentsRead.category, DocumentsRead.downloadedByUser, DocumentsRead.timeDownloaded, DocumentsRead.timeRead
FROM EndUsers.dbo.DocumentsRead DocumentsRead
WHERE (DocumentsRead.fileName Like 'GB') AND (DocumentsRead.category='Notices') AND (DocumentsRead.timeRead Is Null) AND (DocumentsRead.timeDownloaded Between {ts '01/12/2013 00:00:01'} And {ts '08/11/2013 00:00:01'})
输入表如下所示:
看来您的问题出在StartDate / EndDate的格式中。这是您的代码:
MyStr = Format(StartDate, "yyyy/mm/dd")
MyStr = Format(EndDate, "yyyy/mm/dd")
这是我假设您要执行的操作:
StartDate = Format(StartDate, "yyyy-mm-dd")
EndDate = Format(EndDate, "yyyy-mm-dd")
我相信您也可以通过将其包装在#中来告诉Access字符串是一个日期。如
"and DocumentsRead.timeDownloaded >= #" & StartDate & "#" & vbcrlf & _
"and DocumentsRead.timeDownloaded < #" & EndDate & "#"
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句