sql - Excel VBA Selecting Records from Access Database Not Pulling Correctly -

sql - Excel VBA Selecting Records from Access Database Not Pulling Correctly -

i have macro pulls access db , writes recordset spreadsheet based upon dates entered userform. however, if come in in "3/2/2105" , "3/5/2015" returns records 3/2-3/5 , 3/20-3/31. cannot think of reason why this. if point me in right direction/make suggestions appreciated.

sub pullfrommsaccess() queryform.show dim conn object dim rs object dim accessfile string dim sql string dim startdate string dim enddate string dim 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 msgbox "connection 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" if shift1 = "1" sql = "select [line],[area],[shift],[attainment percentage],[date] " & tblname & " shift='1' , date between " & "'" & pastdate & "' " & "and" & " '" & currentdate & "'" end if if shift2 = "2" sql = "select [line],[area],[shift],[attainment percentage],[date] " & tblname & " shift='2' , date between " & "'" & pastdate & "' " & "and" & " '" & currentdate & "'" end if if shift1 = "1" , shift2 = "2" sql = "select [line],[area],[shift],[attainment percentage],[date] " & tblname & " date between " & "'" & pastdate & "' " & "and" & " '" & currentdate & "'" end if end if if tblname = "mditable" if shift1misses = "1" sql = "select [date],[area],[shift],[line],[quantity],[issue] " & tblname & " shift='1' , date between " & "'" & pastdatemisses & "' " & "and" & " '" & currentdatemisses & "'" end if if shift2misses = "2" sql = "select [date],[area],[shift],[line],[quantity],[issue] " & tblname & " shift='2' , date between " & "'" & pastdatemisses & "' " & "and" & " '" & currentdatemisses & "'" end if if shift1misses = "1" , shift2misses = "2" sql = "select [date],[area],[shift],[line],[quantity],[issue] " & tblname & " date between " & "'" & pastdatemisses & "' " & "and" & " '" & currentdatemisses & "'" end if end if on error resume next set rs = createobject("adodb.recordset") if err.number <> 0 set rs = nil set conn = nil msgbox "recordset 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 , rs.bof rs.close conn.close set rs = nil set conn = nil application.screenupdating = true msgbox "there no records in recordset!", vbcritical, "no records" exit sub end if = 0 rs.fields.count - 1 sheet2.cells(1, + 1) = rs.fields(i).name next 'copy recordset excel , reset sheet2.range("a2").copyfromrecordset rs rs.close conn.close set rs = nil set conn = nil msgbox "the records " & pastdate & " , " & currentdate & " retrieved '" & tblname & "' table!", vbinformation, "done" end if phone call trimall end sub

you have field named date, seek renaming , reworking code in first instance that's reserved word , bad thought starters!

when working dates, see allen browne's comments on matter here consistency; http://allenbrowne.com/ser-36.html

you have dates declared string, in sql query you're surrounding them ' not #. should read;

date between " & "#" & pastdate & "# " & "and" & " #" & currentdate & "#"

all of above should sort out, if not comment , i'll take much closer you!

sql vba