ADODB记录集SQL在Excel VBA中返回错误
ADODB记录集SQL在Excel VBA中返回错误
提问于 2017-04-28 02:13:46
Set objClubSalesSourceConn = CreateObject("ADODB.COnnection")
objClubSalesSourceConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDirectory & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
Set rsClubOrders = CreateObject("ADODB.Recordset")
strSQL = "SELECT"
strSQL = strSQL & vbCrLf & " ClubSales.[Order Number] AS OrderNum,"
strSQL = strSQL & vbCrLf & " ClubSales.[Submitted Date] AS SaleDate,"
strSQL = strSQL & vbCrLf & " ClubSales.[Product SKU] AS SKU,"
strSQL = strSQL & vbCrLf & " ClubSales.[Product Name] AS ItemDesc,"
strSQL = strSQL & vbCrLf & " ClubSales.[Ext Item Price] AS SaleAmt,"
strSQL = strSQL & vbCrLf & " ClubSales.[Ext Item Shipping] AS ShipAmt,"
strSQL = strSQL & vbCrLf & " CASE WHEN ISNULL(ClubSales.[Ship Date])"
strSQL = strSQL & vbCrLf & " THEN 1"
strSQL = strSQL & vbCrLf & " ELSE 0 END AS ShipDateNull,"
strSQL = strSQL & vbCrLf & " ClubSales.[Ship Date] AS ShipDate,"
strSQL = strSQL & vbCrLf & " ClubSales.[Pickup Date] AS PickupDate,"
strSQL = strSQL & vbCrLf & " ClubSales.[Quantity Sold] * ClubSales.[Cost Of Goods] AS COGSAmt"
strSQL = strSQL & vbCrLf & "FROM"
strSQL = strSQL & vbCrLf & " ClubSalesSource.csv AS ClubSales"
rsClubOrders.Open strSQL, objClubSalesSourceConn, adOpenDynamic*问题出在SQL中的WHEN语句中。如果我省略了这一点，记录集就会正常打开。我在Excel VBA中查询csv文件。我需要知道怎么解决这个问题。我也尝试过像在Access中那样使用IIF函数，但没有成功。
回答 1
Stack Overflow用户
发布于 2017-04-28 02:59:08
strSQL = "SELECT" & _
" ClubSales.[Order Number] AS OrderNum," & _
" ClubSales.[Submitted Date] AS SaleDate," & _
" ClubSales.[Product SKU] AS SKU," & _
" ClubSales.[Product Name] AS ItemDesc," & _
" ClubSales.[Ext Item Price] AS SaleAmt," & _
" ClubSales.[Ext Item Shipping] AS ShipAmt," & _
" CASE WHEN ISNULL(ClubSales.[Ship Date])" & _
" THEN 1" & _
" ELSE 0 END AS ShipDateNull," & _
" ClubSales.[Ship Date] AS ShipDate," & _
" ClubSales.[Pickup Date] AS PickupDate," & _
" ClubSales.[Quantity Sold] * ClubSales.[Cost Of Goods] AS COGSAmt" & _
" FROM" & _
" ClubSalesSource.csv AS ClubSales"页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43665163
复制相关文章
相似问题
腾讯云开发者
