Run Query with a crieria

  • Thread starter Thread starter Hawk
  • Start date Start date
H

Hawk

I can get a count of the rcords in a select query like
this:

Set db = CurrentDb
Set rst = db.OpenRecordset("qryVerifyTestStatus")

intCount = rst.RecordCount

However, I would like to count the records that the query
returns when a criteria is passed to the 'Lot' field. How
do I pass a criteria in VBA when I run the query?

For example: count records from the query where Lot = 12

Please realize that I am a newbie when giving any
explanations. TIA...
 
I'd suggest using the DCount() function.

eg) DCount("myKeyField","myTable","[Lot]=12")
OR
eg) x = DCount("myKeyField","MyTable","[Lot]=" & lngLot)

If you really want to use a recordset, just remember that
although a QueryDef object allows users to conveniently
save and edit SQL statements, you don't have to base your
recordsets on them. Below is an example of building a SQL
statement via code.

eg) x = CountLot(12)

Function CountLot(lngLot as Long) as Long
Dim strSQL
Dim rst as DAO.Recordset
strSQL = "SELECT myTable.* " _
& "FROM myTable " _
& "WHERE [Lot]=" & lngLot
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenSnapshot)
rst.MoveLast
CountLog = rst.RecordCount
rst.Close
Set rst = Nothing
End Function
 
Hi Elwin

Any chance of configuing your newsreader to add the standard "Re:" prefix?
This helps distinguish original posts, from later replies.

Cheers :-)
TC


Elwin said:
I'd suggest using the DCount() function.

eg) DCount("myKeyField","myTable","[Lot]=12")
OR
eg) x = DCount("myKeyField","MyTable","[Lot]=" & lngLot)

If you really want to use a recordset, just remember that
although a QueryDef object allows users to conveniently
save and edit SQL statements, you don't have to base your
recordsets on them. Below is an example of building a SQL
statement via code.

eg) x = CountLot(12)

Function CountLot(lngLot as Long) as Long
Dim strSQL
Dim rst as DAO.Recordset
strSQL = "SELECT myTable.* " _
& "FROM myTable " _
& "WHERE [Lot]=" & lngLot
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenSnapshot)
rst.MoveLast
CountLog = rst.RecordCount
rst.Close
Set rst = Nothing
End Function

-----Original Message-----
I can get a count of the rcords in a select query like
this:

Set db = CurrentDb
Set rst = db.OpenRecordset("qryVerifyTestStatus")

intCount = rst.RecordCount

However, I would like to count the records that the query
returns when a criteria is passed to the 'Lot' field. How
do I pass a criteria in VBA when I run the query?

For example: count records from the query where Lot = 12

Please realize that I am a newbie when giving any
explanations. TIA...
.
Click to expand...
Click to expand...
 
Back
Top