Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Access 2007 query results different than vba sql results

Avatar of williecg
williecgFlag for United States of America asked on
Microsoft Access
6 Comments1 Solution445 ViewsLast Modified:
Hello experts this one really has me stumped.

Access 2007 query results are different than vba sql results.

Query produces one record.  This is the correct result.

VBA SQL produces 3 records.  There are 3 records in the table.

There are 4 fields in the table.
  T_ID is autonumber, indexed, no dups
  D_ID is number, indexed, dups ok
  The other 2 are text
When the D_ID field in NOT indexed the vba sql produces 1 record, the same as the query.

When the D_ID field IS indexed, the vba sql produces 3 records, the query produces 1 record.

Here is the sql that I ran from the query
============================================
SELECT [00_test2].T_ID
FROM 00_test2
WHERE ((([00_test2].LR_Eye)='Left') AND (([00_test2].Tissue_Type)='Whole Globe') AND (([00_test2].D_ID)=479300912));
============================================

Here is the VBA sql
============================================
strsql = _
"SELECT [00_test2].T_ID FROM 00_test2 WHERE ( (LR_Eye) = 'Left' AND ((Tissue_Type)= 'Whole Globe') AND ((D_ID)= 479300912)) "

MsgBox ("/.............  " & strsql)

Set rst = db.OpenRecordset(strsql)

MsgBox (rst.RecordCount)
rst.Close
============================================

Thanks this is making me crazy.

Cliff