Like clause, 2501 records and a major headache...

[Don Whiting]

Situation:

BaseTable contains 2501 records that all look like this:
FieldX FieldY
-----------------------
HDA.txt 12
HDA.txt 12
HDA.txt 12
...

(both fields are text)



I have 2 queries:

A - select * from BaseTable
where (FieldX like "HD*.txt");

B - select * from A
where FieldY = "12";

Query B returns zero records.

If I take a record out of the base table, I get 2500
records.
If I add a record to the base table, I get 2502 records.
If there are 2501 records, I get zero. Nada. Zilch.

I've search the MS knowledge base and see nowhere that
mentions this as a bug. I've searched the Internet and
nobody seems to have come across this problem. This is
such an incredibly simple thing I can't believe we were
the first to notice this error.

As it happens, this caused a major production problem and
my boss has asked me to ensure that this never takes place
again. But we have this sort of thing through our system.

 

[Chris]

Try repairing the database. It sounds like an index has
been screwed.

 

[amoskevitz]

This error is confirmed, it exists in brand spanking new databases, and still exists as of Access 2010, both in .mdb and .accdb.
Furthermore it exists at any multiple of 2501 (5002, 7503, etc)
The code below pasted into a clean database will demonstrate the issue
it works by recording everytime the query returns no records
first run setuptables to create the tables, then run runme with the number of loops you want to try, (the default of 25020 will take a while to run)

Option Compare Database
Const tblname As String = "tbltest2"
Const tblcountername As String = "tbltestcounter2"
Sub setuptables()
'only run this once
Dim tbldef As TableDef
Dim fld As Field
Dim idx As Index
'create the test table
'delete it if it already exists
'create a tabledef
Set tbldef = CurrentDb.CreateTableDef(tblname)
'add the fields
Set fld = tbldef.CreateField("ID", dbLong)
tbldef.Fields.Append fld
Set fld = tbldef.CreateField("Value", dbText, 50)
tbldef.Fields.Append fld
Set idx = tbldef.CreateIndex("idx_val")
'add an index on only one field
idx.Fields.Append idx.CreateField("Value", dbText, 50)
idx.Unique = False
tbldef.Indexes.Append idx
'save the table in the database
CurrentDb.TableDefs.Append tbldef
'create the counter table
Set tbldef = CurrentDb.CreateTableDef(tblcountername)
'add the fields
Set fld = tbldef.CreateField("record", dbLong)
tbldef.Fields.Append fld
Set fld = tbldef.CreateField("FindDate", dbDate)
tbldef.Fields.Append fld
Set fld = tbldef.CreateField("accessVersion", dbText, 50)
tbldef.Fields.Append fld
Set fld = tbldef.CreateField("DAOVersion", dbText, 50)
tbldef.Fields.Append fld
CurrentDb.TableDefs.Append tbldef
End Sub
 
Sub runme(Optional loopcount As Long = 25020)
Dim rs As Recordset
Dim timestamp As Date
Dim DAOversion As String
Dim AccessVersion As String
'get the versions of the engines for logging
AccessVersion = CurrentDb.Properties("AccessVersion")
DAOversion = DBEngine.Version
Set rs = CurrentDb.OpenRecordset(tblname)
Set rs3 = CurrentDb.OpenRecordset(tblcountername)
CurrentDb.Execute ("DELETE * FROM " & tblname)
  rs.AddNew
    rs!id = 0
    rs!Value = "AC"
  rs.Update
  rs.AddNew
    rs!id = 1
    rs!Value = "AB"
  rs.Update
For counter = 2 To loopcount
  rs.AddNew
  '  rs!id =
    rs!Value = "AB"
  rs.Update
 
  strsql = "SELECT tt.ID, tt.Value FROM " & tblname & " as tt " & _
            "WHERE (((tt.ID)=1) AND ((tt.Value) Like 'a*b*'));"
  Set rs2 = CurrentDb.OpenRecordset(strsql)
 
  'if there are no records in the query store the loop count
  If rs2.BOF And rs2.EOF Then
    rs3.AddNew
      rs3!record = counter
      rs3!FindDate = timestamp
      rs3!DAOversion = DAOversion
      rs3!AccessVersion = AccessVersion
    rs3.Update
  End If
 
Next counter
 
End Sub