Sorry if this is a duplicate post, but after my first attempt I did not see it was submitted...
I am importing records from an Excel file into an Access database via VB. The import is importing records, but my WHERE clause is not working and all records in my excel file are importing when I execute the script. I believe the issue is that the fields in my excel file are text and I am trying to filter on a numeric field and a date field. I tried different variations of Convert and Cast, but I keep getting errors.
I am trying to import records where the entry number in the Excel file ([Order Form link].F73) is > Max Entry ID in my Access database AND Order Date in the Excel file ([Order Form link].F1) is > StartDate.
Here is a snippet of code that is importing all records in my Excel file and not filtering on the WHERE clause:
Private Sub cmdImportRecords_Click()
Dim rs As Recordset
Dim intLastEntryID As Integer
Dim StrCount As String
Dim startdate As Date
datestartdate = Me.txtStartDate.Text
' MsgBox (datestartdate)
If vbYes = MsgBox("Are you Sure you want to Import Records from Excel Spreadheet into Current Months Orders?", vbYesNo + vbQuestion, "Close Week?") Then
Set MyDB = CurrentDb
'Get the max Entry ID from CurrentMonthORders
Set rs = MyDB.OpenRecordset("SELECT Max(CurrentMonthOrders.[Entry_ID]) AS MaxOfEntry_ID FROM CurrentMonthOrders")
If IsNull(rs!MaxofEntry_ID) = True Then
strLastEntryID = "0"
strLastEntryID = rs!MaxofEntry_ID
' MsgBox (strLastEntryID)
StrCount = "0"
Set rs = MyDB.OpenRecordset("SELECT * " & _
" FROM [Order Form link] WHERE [Order Form link].F73 > '" & intLastEntryID & "' AND [Order Form link].F73 <> 'Entry ID' AND [Order Form link].F1 > '" & startdate & "'")
Do Until rs.EOF
MyDB.Execute ("INSERT INTO CurrentMonthOrders" & _
Any help with correct syntax will be greatly appreciated.