Excel VBA Selecting Records from Access Database Not Pulling Correctly

Question

I have a macro that pulls from an Access DB and writes the recordset to the spreadsheet based upon dates that are entered into a userform. However, if I enter in "3/2/2105" and "3/5/2015" it returns all the records from 3/2-3/5 and then 3/20-3/31. I cannot think of any reason why it would do this. If anybody could point me in the right direction/make suggestions it would be greatly appreciated.

Sub pullfrommsaccess()

    queryform.Show

    Dim conn As Object
    Dim rs As Object
    Dim AccessFile As String
    Dim SQL As String
    Dim startdate As String
    Dim enddate As String
    Dim i As Integer


    Sheet2.Cells.Delete
    Application.ScreenUpdating = False
    AccessFile = ThisWorkbook.Path & "\" & "mdidatabase.accdb"


    On Error Resume Next
    Set conn = CreateObject("ADODB.connection")
    If Err.Number <> 0 Then
        MsgBox "Connection was not created!", vbCritical, "Connection Error"
        Exit Sub
    End If
    On Error GoTo 0

    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile


If tblname = "Attainments" Then
    If shift1 = "1" Then
        SQL = "SELECT [Line],[Area],[Shift],[Attainment Percentage],[Date] FROM " & tblname & " WHERE Shift='1' and Date Between " & "'" & pastdate & "' " & "and" & " '" & currentdate & "'"
    End If
    If shift2 = "2" Then
        SQL = "SELECT [Line],[Area],[Shift],[Attainment Percentage],[Date] FROM " & tblname & " WHERE Shift='2' and Date Between " & "'" & pastdate & "' " & "and" & " '" & currentdate & "'"
    End If
    If shift1 = "1" And shift2 = "2" Then
        SQL = "SELECT [Line],[Area],[Shift],[Attainment Percentage],[Date] FROM " & tblname & " WHERE Date Between " & "'" & pastdate & "' " & "and" & " '" & currentdate & "'"
    End If
End If

If tblname = "MDItable" Then
    If shift1misses = "1" Then
        SQL = "SELECT [Date],[Area],[Shift],[Line],[Quantity],[Issue] FROM " & tblname & " WHERE Shift='1' and Date Between " & "'" & pastdatemisses & "' " & "and" & " '" & currentdatemisses & "'"
    End If
    If shift2misses = "2" Then
        SQL = "SELECT [Date],[Area],[Shift],[Line],[Quantity],[Issue] FROM " & tblname & " WHERE Shift='2' and Date Between " & "'" & pastdatemisses & "' " & "and" & " '" & currentdatemisses & "'"
    End If
    If shift1misses = "1" And shift2misses = "2" Then
        SQL = "SELECT [Date],[Area],[Shift],[Line],[Quantity],[Issue] FROM " & tblname & " WHERE Date Between " & "'" & pastdatemisses & "' " & "and" & " '" & currentdatemisses & "'"
    End If

End If



    On Error Resume Next
    Set rs = CreateObject("ADODB.Recordset")
    If Err.Number <> 0 Then
        Set rs = Nothing
        Set conn = Nothing
        MsgBox "Recordset was not created!", vbCritical, "Recordset Error"
        Exit Sub
    End If
    On Error GoTo 0


    rs.CursorLocation = 3
    rs.CursorType = 1

    rs.Open SQL, conn


    If rs.EOF And rs.BOF Then
        rs.Close
        conn.Close
        Set rs = Nothing
        Set conn = Nothing
        Application.ScreenUpdating = True
        MsgBox "There are no records in the recordset!", vbCritical, "No Records"
        Exit Sub
    End If

    For i = 0 To rs.Fields.Count - 1
        Sheet2.Cells(1, i + 1) = rs.Fields(i).Name
    Next i


    'Copy From RecordSet to Excel and Reset
    Sheet2.Range("A2").CopyFromRecordset rs
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing



    MsgBox "The records from " & pastdate & " and " & currentdate & " were successfully retrieved from the '" & tblname & "' table!", vbInformation, "Done"
End If



Call TrimALL

End Sub

Unrelated, but I'd suggest you drop alllowercase and adopt a camelCase naming convention for your locals, and PascalCase for procedure names. Readability++ — Mathieu Guindon
– Mathieu Guindon, Commented Apr 9, 2015 at 17:02
Thanks for the tip. I know I'm pretty bad about making my code pretty, Im the only one who looks at it at my job usually haha — AggieIE
– AggieIE, Commented Apr 9, 2015 at 17:05

Mark Horner · Accepted Answer · 2015-04-09 17:36:19Z

1

You have a field named Date, try renaming that and reworking the code as in first instance that's a reserved word and is a bad idea for starters!

When working with dates, see Allen Browne's comments on the matter here for consistency; http://allenbrowne.com/ser-36.html

You have your dates declared as string, but in your SQL query you're surrounding them with a ' not a #. It should read;

Date Between " & "#" & pastdate & "# " & "and" & " #" & currentdate & "#"

All of the above should sort you out, if not comment and I'll take a much closer look for you!

answered Apr 9, 2015 at 17:36

Mark Horner

1181 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AggieIE Over a year ago

I implemented the code and it still didn't work :/ I think the field named Date is fine, because of the brackets surrounding it, which has worked fine in past projects. Thanks for the help though, I really appreciate it!

Collectives™ on Stack Overflow

Excel VBA Selecting Records from Access Database Not Pulling Correctly

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related