excel - 为什么我的 ADO Access 连接给出的结果与在 Access 应用程序中运行的相同查询不同？

Question

一天多来我一直试图弄清楚这一点，我觉得答案归结为 Access 如何处理数据类型，但我仍然没有弄清楚。我已经简化了这个问题，希望它会更容易解决。我在 Excel 工作簿中有 2 列 - A 列是文本，B 列是日期。基本上我只想在 A 列不为空时从 B 列中选择日期。这在理论上可以正常工作，但不适用于我遇到的实际情况。

例如:B 列不是所有日期，而是 B 列的值之一只是一个空字符串。为了解决这个问题，我简单地添加了一个 IIF 语句，如果 B 列是 =''设为 0:

querystr = "Select IIF([Enroll Date] = '',0,[Enroll Date]) As vdate FROM [Sheet1$] where [Account Number] IS NOT NULL"

当我将工作表导入 Access 时，此查询工作得非常好，但是当我将它与 Excel VBA 的 ADO 连接一起使用时，所有记录都返回为 Empty ，除了返回为空的零长度字符串。这个子将为您设置整个工作簿并演示整个事情:

Sub flow()

Dim con As Object
Dim rec As Object
Dim path As String
Dim y As Integer: y = 1
Dim sht1 As Worksheet

Set sht1 = ActiveSheet

path = SetData()

Set con = CreateObject("ADODB.Connection")
Set rec = CreateObject("ADODB.Recordset")

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";" & _
                            "Extended Properties=" & Chr(34) & "Excel 12.0 Xml;HDR=YES" & Chr(34) & ";"


con.Open

    querystr = "Select IIF([Enroll Date] = '',0,[Enroll Date]) As vdate FROM [Sheet1$] where [Account Number] IS NOT NULL"

rec.Open querystr, con

Range("D2").CopyFromRecordset rec
'every record is blank
rec.Close
rec.Open querystr, con

Do Until rec.EOF
    y = y + 1
    If IsEmpty(rec.Fields("vdate")) Then
        Range("G" & y).Value = "Empty"
    ElseIf IsNull(rec.Fields("vdate")) Then
        Range("G" & y).Value = "Null"
    End If
    rec.movenext
Loop

End Sub

Function SetData() As String

Dim sht1 As Worksheet: Set sht1 = ThisWorkbook.Sheets(1)

    With sht1
        .Columns.Item("A").NumberFormat = "@"
        .Columns.Item("B").NumberFormat = "m/d/yyyy"
        .Range("A1").Value = "Account Number"
        .Range("B1").Value = "Enroll Date"
        .Columns("A:B").AutoFit
        For x = 1 To 5
            .Range("A" & x + 1).Value = x
        Next x
        Union(.Range("B2:B4"), .Range("B6")).Value = DateSerial(2016, 4, 1)
        'change B5 to accounting format
        .Range("B5").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
        .Range("B5").Value = vbNullString
    End With


    With ThisWorkbook
        .Save
        SetData = .FullName
    End With

End Function

Answer 1

更改[Enroll Date] = ''至IsNull([Enroll Date])在您的 IIF陈述。

在这种情况下，我怀疑 [Enroll Date]在 Access 中被格式化为 Date和 Date数据类型的值不能为 '' (或空字符串)。它是有效日期或空值。

也就是说，在 Access 和 Excel 之间传输数据时，您必须在每个系统中找到数据类型才能使其正常工作。我不能在这里给你一个体面的解释，但如果你用谷歌搜索它，你会发现很多关于将数据从 Excel 传输到 Access 时的数据类型问题。