Findfirst matching long integers incorrectly

Question

Objective: Set a recordset using a query string, then perform FindFirst = number to find a specific ID, and therefore record, in the recordset.

Problem: The recordset is generated correctly but FindFirst consistently, incorrectly matches IDs below a certain value.

Description: When I create a recordset inner (or left or right) joining Task and Task_Entry matching specific CycleID and TaskDataID values I get a list of records (in my recordset, I ensure TaskID isn't ambiguous). If I FindFirst any value below 1469, FindFirst returns the 1469 record, every time. See example records and structure below.

Original Query (put into a CurrentDB.OpenRecordset()):
"SELECT Datetime, TaskDataID, SigID, Task_Entry.* FROM Task INNER JOIN Task_Entry ON Task_Entry.TaskID = Task.TaskID WHERE Task.CycleID = " & inputCycleID & " AND Task.TaskDataID = " & inputTaskDataID & " ORDER BY Task.TaskID"

Table Structure:

• Two tables bound via autonumber ID field:
• Task contains general task information (date time, signature ID, etc)
• Task_Entry contains a subset of Entry specific information
• Not all records in Task are related to Task_Entry

Example records:
Task: TaskID, Datetime, TaskDataID, CycleID, SigId
1447 03/09/16 15 7 1495 (the TaskDataID was different)
... other records
1469 03/15/16 15 8 1518
... other records

Task_Entry: TaskID, Data1, Data2
1447 929 930.5
1469 919 922

Troubleshooting:

• Originally performed FindFirst in VBA using: rst.FindFirst "TaskID = " & inputTaskID where inputTaskID was a variant.
  • The datatype did not matter. Forced datatype to Long and still matched 1447 to 1469 somehow.
• Changed recordset to only return TaskID = inputTaskID.
  • "SELECT Datetime, TaskDataID, SigID, Task_Entry.* FROM Task INNER JOIN Task_Entry ON Task_Entry.TaskID = Task.TaskID WHERE Task.TaskID = " & inputTaskID
  • This worked; returns one record matching inputTaskID.
  • I have use cases for returning the entire set the matches CycleID and TaskDataID, so I would still like to know why this doesn't work.

Answer 1

When FindFirst is used on a recordset and results in NoMatch being true, the recordset remains on the current record.

In this specific case, the original query over-constrained the records on the TaskDataID parameter returning a set of records not containing the ID being searched for. Performing FindFirst left the recordset on the first record (which in this case was the one with TaskID = 1469).