I am having a major problem with an SQL statement.  For some reason
whwenever I try to open a recordset it only returns one record though I
know with my test data it should return several.

The following

sql= "SELECT [Code] FROM [Jobs]"
set rs=db.openrecordset(sql)

...results in a recordset with 1 record (which has to be wrong!)

set rs=db.openrecordset("Jobs")

...results in a recordset with all records in the "Jobs" table (>10).

sql= "SELECT DISTINCT [Code] FROM [JOBS]"
set rs=db.openrecordset(sql)

...results in a recordset with 4 records (which is also correct)

What is wrong with my first sql line and how could SELECT DISTINCT ever
produce MORE records than just SELECT.

What's up?

(This code is just a sample of a larger issue which I need to stick to-
so please don't suggest alternate methods."

Thanks,
-Jamie G.

For performance reasons, the jet engine will sometimes only return the
first record in a recordset, generated from a SQL statement.  To get the
true number of records returned, perform a move last to force a read of the
entire table and then check the recordcount.

--
David W. Forest
LAN Specialist
Information Technologies
Lear Corporation - Ford Division
SBN Level 2
http://www.concentric.net/~dforest/

If you're not scared, you don't understand.

You were absolutely correct and I wouldn't have figured it out for days!

-Jamie G.

If your recordset is very big I have found it to be quicker to run a
seperate SQL count function like this:

'===
sSQL = "SELECT COUNT(*) AS NumRecords FROM .... WHERE ...."

Set MyRS = MyDB.OpenRecordset(sSQL,dbOpenSnapshot)

lRecordCount = CLng(MyRS!NumRecords)

MyRS.Close
'===

Jon.

--

  |   |  | |  | Snail: 34 West Street, Banbury, Oxon, OX16 7HD, UK
\_/   \__/ |  | Phone: +44 1295 265109  http://www.evansoft.demon.co.uk/