Count records in query

i want to count the number of records in a query without having to run a seperate query. i think i've jsut simply fogotten how to do this. this is the SQL string i had:

SELECT Count(ID) AS gf_Count, ID, Title, Blurb, Author, gf_Cat FROM gfaith WHERE (((Body) Like '%God%')) ORDER BY gf_Cat, Beginning;

the page came up with this error

Microsoft JET Database Engine error '80040e21'

You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function.

/search.asp, line 215

Line 215 is where the recordset is opened and the SQL Query is run.

any ideas?

still nothing. i even tried running the query in Access changing the wildcards to '*'. still got the same error message, that

You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function.

ok, i got it changing what michael had from

SELECT (Select Count(id) from gf_Cat) AS gf_Count, gf_Cat.Title, gf_Cat.Blurb, gf_Cat.Authorgf_Cat FROM gf_Cat WHERE (((gf_Cat.Body) Like '%god%'));

to

SELECT (Select Count(id) from gfaith Where (((gfaith.Body) Like '*god*'))) AS gf_Count, gfaith.Title, gfaith.Blurb, gfaith.Author, gfaith.gf_Cat
FROM gfaith
WHERE (((gfaith.Body) Like '*god*'));

the table name was gfatih, and gf_Cat is a field. i made it work to my purposes by nesting a second query into the frist query.

i would have used RecordCount, but that only returned a result of -1 which means it was unable to count the recordset. but this method works

As michael noted, nested/sub queries can blow your performance, but the extent depends upon your db structure.

You may want to use a couple of timers to check the performance differences between the above query, and running a separate query and just checking the rs.RecordCount property.

Also, the type of rs you create will impact performance. In general, a forward_only rs will be faster to create, but should be suitable for getting a record count.