capturing a count query in a variable?

[pdbowling]

Hi all, this shouldn't be too difficult, I just don't know the syntax.

I tried to do this in the code for a form.

deptCount = DoCmd.OpenQuery("countDepts")

and it won't compile. How am I supposed to do this?

I need to capture this number in a variable because I'll be using it in loops and Range Selection

Thanks so much.
PB

 

[ray705]

This may not be the only way but here is how I do that.

   Set MyDB = CurrentDb
   Set MyTable = MyDB.OpenRecordset(EmployeeTable, dbOpenTable)  ' Open table.
   MyTable.MoveLast
   intEEcount = MyTable.RecordCount

 

[Shadez]

First u need to dicide what ur going to use

ADO or DAO

ADO, appoach

Dim rs as new adodb.recordset

Rs.open "Select * from countDepts", currentproject.connection deptCount = rs!FieldName
rs.close

OR

DAO, Aproach

deptCount=CurrentDb.QueryDefs("countDepts").fields("FieldName")


I prefer to use ADO cos its more flexable but the choise is up to U

ShadeZ

 

[FoFa]

Have you cocsidered
DCOUNT("column2count","tableORquery","criteria")

just a thought

 

[Shadez]

I dont like dcount its slow and doesnt alwasys work,

But if it works for you give its a go.



ShadeZ

 

[FoFa]

True the agregate functions are slower (I wonder why), but I have never had one not work unless I had an error in it. And it is simple to use, specially if you want to fill a field on a form with the data.

 

[Shadez]

I have had Dcount error on me, without any error in the query

the query ran fine from the query builder, but from dcount itwouldnt work. (Somthing to do with date values i was taking from another form)

But like i said earlier if it works and ur happy no problem.



ShadeZ