Simple problem - please help if you can!!

[Ranj]

Can anyone help me with the following please?
I'm trying to count the number of records in a table and use this calculated value elsewhere in my module. I have been using the 'SELECT Count (*)...FROM' statement, but when I use the DoCmd.RunSQL on the query it complains that it's invalid. This is because I have to use a temporary table to store the counted value. (and use the command SELECT Count * INTO....FROM)

Is there a way of getting around this so that I don't have to use temporary tables?

Thanks in advance,

Ranj.

 

[Karl Blessing]

well if you are using ADO in a certain programming language, rather than count(*) whic has always messed with me, this is the generic command

recordset.recordcount

depending if you are only trying to do it Query based, my method is programatic, key point to keep in mind is that I use a static cursor, and a readonly lock, the defaul type doesnt allow you to return a recordcount, instead it'll return -1.

let me know if you are trying to do this by query only, or if you are using some programatic inteface such as VB or C++.
Karl
kb244@kb244.com

http://www.kb244.com

Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

 

[KMITCH]

Try using the DCount function. This saves
you from having to instantiate a recordset.
Here's an example from the Access 97 help:

Syntax: DCount(expr, domain[, criteria])

Example:

intX = DCount("[ShippedDate]", "Orders", _
"[ShipCountry] = 'UK' AND [ShippedDate] > #1-1-95#&quot