SQL Command Code Acting Odd

  • Thread starter Thread starter CompGeek78
  • Start date Start date
C

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
 
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
 
Back
Top