Proper syntax to convert text fields to number and date in WHERE clause

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

Me.txtStartDate.SetFocus

    datestartdate = Me.txtStartDate.Text
   
' MsgBox (datestartdate)
   
DoCmd.Beep
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")

   rs.MoveFirst
   If IsNull(rs!MaxofEntry_ID) = True Then
   strLastEntryID = "0"
   Else
   strLastEntryID = rs!MaxofEntry_ID
   End If
   
 ' MsgBox (strLastEntryID)

rs.Close

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.
btkriegerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Let's simplify.
Instead of importing the spreadsheet, link to it.  That will tell you if Access is seeing the date field as a date or as a string.

Once you know how Access is seeing the date, we can proceed.  In either case we will append the data from the spreadsheet to the primary table.  The Where clause will have to either convert the linked spreadsheet to date or not.

a. Where [Order Date] >= Forms!yourform!txtStartDate
b. Where cDate([Order Date]) >= Forms!yourform!txtStartDate

For this to work correctly, the unbound control - txtStartDate must have its format set to Short Date.  This will tell Access that the control should only accept valid dates and also cause it to display a date picker.  It also means that in the Where clause in the query, txtStartDate will be handled as a date.

I am mystified by the code surrounding finding and using the ID.

And finally, don't use the .text property unless your code is running in a control level event where the .text is a property of the control you are IN.  In earlier versions of Access, the .text property was not available when the focus was in some other control.  That is why  the code you copied had you setFocus to the control before referencing its .text property.  The .value property is the default property and would you would be using 99% of the time.  Since it is the default, you can omit it so the control reference would be -- Me.txtStartDate
0
Gustav BrockCIOCommented:
Many errors - do use Option Explicit  in your module - and no need to run a query for each record.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intLastEntryID As Integer
Dim StrCount As String
Dim startdate As String
Dim SQL As String
   
DoCmd.Beep
If vbYes = MsgBox("Are you Sure you want to Import Records from Excel Spreadheet into Current Months Orders?", vbYesNo + vbQuestion, "Close Week?") Then

    Set db = CurrentDb
    startdate = Format(Me!txtStartDate.Value, "yyyy\/mm\/dd")

    ' Get the max Entry ID from CurrentMonthORders.
    intLastEntryID = Nz(DMax("[Entry_ID]", "CurrentMonthOrders"), 0)

    SQL = "INSERT INTO CurrentMonthOrders " & _ 
        "SELECT * " & _
        "FROM [Order Form link] " & _
        "WHERE [Order Form link].Fxx  > " & intLastEntryID & " AND [Order Form link].Fyy <> 'Entry ID' AND [Order Form link].F1 > #" & startdate & "#")
    db.Execute (SQL)

End If

Open in new window

Do replace Fxx and Fyy with two different field names.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
btkriegerAuthor Commented:
Gustav,

Thank you for your assistance.  I updated as you stated above and there are still 29 records importing where they should not be. The Entry ID's imported are ID #'s 71 - 99.  I have 669 records in my Excel file with Entry ID's ranging from 37 to 706.

I removed:  [Order Form link].F1 > #" & startdate & "#" from the WHERE clause and had the same 29 records imported again.
I replaced above and removed:  [Order Form link].F73 > '" & intLastEntryID and all 669 records imported.

I have the Entry ID declared as an int and my entry date as a Date. Both fields are text in my Excel file. Do I need to CONVERT or CAST the fields from my excel file when comparing values?  I am getting an error when I am trying that: Undefined function 'Convert' in expression

I appreciate your help!
0
Gustav BrockCIOCommented:
Can't tell. I don't have your file.

Your only option is to carefully study those records you want to exclude, to find out how they differ from the rest; then adjust the WHERE clause to filter them out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.