SQL Command Code Acting Odd

[CompGeek78]

Alright, here's an odd one from an Access database I'm running.

I have a SQL query:
SELECT * FROM [Service Schedule] WHERE ID=2 AND Volume <= 3000 AND
Term='Monthly' and special = 'Regular' ORDER BY volume

When I put that into the SQL view of the query builder, I get 2
records, one with a volume of 0 and one with a volume of 3000.

When I use this code:
sSQL = "SELECT * FROM [Service Schedule] WHERE ID=2 AND Volume <= 3000
AND Term='Monthly' and special = 'Regular' ORDER BY
volume"
Set rsServiceSched = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset,
dbSeeChanges)

I only get 1 record, the one with the volume of 0.

Here's where it gets really strange...

When I change Volume <= 3000 to Volume < 3000 I get one record (volume
= 0)
When I change Volume <= 3000 to Volume = 3000 I get one record (volume
= 3000)

Anyone spot anything blatantly wrong with what I'm doing?

Keven Denen

 

[John Spencer]

How do you know you only get one record?

If you are using rsServiceSched.RecordCount you must force Access to loadd all
the records by moving to the last record

sSQL = "SELECT * FROM [Service Schedule] WHERE ID=2 AND Volume <= 3000
AND Term='Monthly' and special = 'Regular' ORDER BY
volume"
Set rsServiceSched = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset,
dbSeeChanges)

If rsServiceSched.RecordCount > 0 then
rsServiceSched.MoveLast
rsServiceSched.movefirst 'might as well move back
msgbox rsServiceSched.RecordCount & " records in recordset"
End If

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County