asked on

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.

PatHartman

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

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

btkrieger

ASKER

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!

Gustav Brock

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.