Dcount on SQL query

[Guest]

I am curious about using the Dcount function on a query that isn't "stored"
and named...
the query runs in the AfterUpdate of a textbox... the user enters the search
criteria and the results are returned on a subform. this works fine.
now, how to count the records in the results that have a CategoryID=1?

when I try to use the DCount function, what do I use as the domain??

....Dcount("[CategoryID]", "WHAT HERE???", "[CategoryID]=1")

 

[Ken Snell [MVP]]

DCount will work only for stored tables and stored queries. You likely will
need to open a recordset based on the desired SQL string, move to the last
record, and then get the .RecordCount value.

 

[Alex Dybenko]

Hi,
What do you mean with isn't "stored" and named? if you have only sql - then
you can reate w a new querydef, store a query and then run dcount. or better
open recordset, and pass sql with count there

 

[Guest]

Thanks Ken... I will make another attempt with your advice in mind.

DCount will work only for stored tables and stored queries. You likely will
need to open a recordset based on the desired SQL string, move to the last
record, and then get the .RecordCount value.

--

Ken Snell
<MS ACCESS MVP>

I am curious about using the Dcount function on a query that isn't "stored"
and named...
the query runs in the AfterUpdate of a textbox... the user enters the
search
criteria and the results are returned on a subform. this works fine.
now, how to count the records in the results that have a CategoryID=1?

when I try to use the DCount function, what do I use as the domain??

...Dcount("[CategoryID]", "WHAT HERE???", "[CategoryID]=1")

 

[Pat Hartman\(MVP\)]

To get a count of the records in your subform's RecordSource, just add a
footer to the subform and add a control with the following ControlSource:
=Count(*)

The footer doesn't need to be visible on the main form if it will look
awkward. You can get the value of this count field and display it on the
mainform as follows:
=subformname.Form.controlname

No code, no query

 

[Tim Ferguson]

the user enters the search
criteria and the results are returned on a subform. this works fine.
now, how to count the records in the results that have a CategoryID=1?

if you still have access to the original SQL, then it's not too hard to
wrap it in a count():


Set rst = CurrentDb().OpenRecordset("select count(*) from (" & _
mySource & _
") where CategoryID=1", _
dbOpenSnapshot, dbForwardOnly)

Debug.Print rst.Fields(0).Value
rst.Close

Otherwise, you'll just have to hack into

me.controls("mysubform").form.recordsetclone

and apply a .Filter; and then .moveLast it and read its .RecordCount, or
something like that.

If you have access to the basic search criteria, I think I'd build a
proper DCount() criterion string or, at worst, a whole SQL COUNT() query.

Hope that helps

Tim F