SQL statement

[parry]

Hi, Im trying to understand some basic SQL. I have created a button on a form and the code to this is below. What I want to do is count the number of records in Table1 where the Played field is equal to true.

I did a normal query then looked at the SQL View and it had this...
SELECT Table1.Played
FROM Table1
WHERE (((Table1.Played)=True));

Private Sub Command125_Click()
Dim MyDb As Database, Rs As Recordset
Set MyDb = CurrentDb
Set Rs = MyDb.OpenRecordset("Select Table1.Played from Table1 where Table1.played =true")
MsgBox "There are " & Rs.RecordCount & " games that have been played"
End Sub

It always comes up with the answer 1 where it should be 50.

I have tried just counting the number of records in the table using the following code which worked so I must be doing something right Im just stuck on the select bit. Any ideas?

Private Sub Command125_Click()
Dim MyDb As Database, Rs As Recordset
Set MyDb = CurrentDb
Set Rs = MyDb.OpenRecordset("Table1")
MsgBox "There are " & Rs.RecordCount & " records in the table"
End Sub

 

[bat17]

try this mod:

Private Sub Command125_Click()
Dim MyDb As Database, Rs As Recordset
Set MyDb = CurrentDb
Set Rs = MyDb.OpenRecordset("Select Table1.Played from Table1 where Table1.played =true")
Rs.MoveLast
MsgBox "There are " & Rs.RecordCount & " games that have been played"
End Sub

By using MoveLast you force the query to load all the data and then hopefully it will count all of the records

Peter

 

[parry]

try this mod:

Private Sub Command125_Click()
Dim MyDb As Database, Rs As Recordset
Set MyDb = CurrentDb
Set Rs = MyDb.OpenRecordset("Select Table1.Played from Table1 where Table1.played =true")
Rs.MoveLast
MsgBox "There are " & Rs.RecordCount & " games that have been played"
End Sub

By using MoveLast you force the query to load all the data and then hopefully it will count all of the records

Peter

Cheers thanks Peter that worked a treat! Do you know why didnt it just work then instead of having to go to the last record. Do select queries always do this sort of thing (ie am I always going to have to use movelast before getting info from a recordset)?

 

[bat17]

You only need the movelast if you want to count the records. From help for RecordCount

Use the RecordCount property to find out how many records in a Recordset or TableDef object have been accessed. The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only–type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. You can also use an SQL Count function to determine the approximate number of records your query will return.

HTh

Peter

 

[Russell Hauf]

How about using one line of code and avoiding recordsets altogether?

MsgBox "There are " & DCount("*", "Table1","[Played]=true") & " games that have been played"

HTH,

Russell

 

[parry]

Thanks Russell. Ive got a MAJOR problem now. ecuase this is a test I decided to copy the form and pasted as a new form name. Now whenever I open the database I get the following error...

Error accessing file. Network connection may have been lost.

I select Help and it tries to open Help but comes up with a blank window (no menu or anything). This is major because I cant edit any VBA on any form/module as this error appears again and again.

I tried compacting/repairing to no avail. Im not connected to a network at all - it must be to do with the recordset. I tried deleting the code and the copied form but it wont let me.

Im getting worried. This database took me a long time to create.

 

[Russell Hauf]

Hmm. I would try creating a new database and importing all your tables, queries, forms, modules, etc., and see if that works.

Hope it helps,

Russell

 

[parry]

Thanks Russell. I searched google and found this article which explains the problem. What a wank, Im going to have to **** around and export everything as you suggested. Bloody M$ and their band-aid coding.