Counting records

[Jeff]

I havea query where I am counting records in a table and I want
to perform an operation on the table if this value is greater than 1...

In my query, CountRecs_qry, my SPL looks like this:

SELECT Count(*) AS Expr1
FROM TEMP_tbl;

What sort of command do I need to put in my event procedure to check if the
value of Expr1 > 0?

Thanks

 

[Douglas J. Steele]

You'd probably be better off using DCount:

If DCount("*", "TEMP_tbl") > 0 Then
' It's greater than zero...
Else
' It's equal to zero
End If

 

[MGFoster]

I havea query where I am counting records in a table and I want
to perform an operation on the table if this value is greater than 1...

In my query, CountRecs_qry, my SPL looks like this:

SELECT Count(*) AS Expr1
FROM TEMP_tbl;

What sort of command do I need to put in my event procedure to check if the
value of Expr1 > 0?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you mean SQL not SPL .

You'll have to create a Recordset to "see" if the count > 0. Like this:

dim db as dao.database, rs as dao.recordset, qd as dao.querydef

set db = currentdb
set qd = db.querydefs("CountRecs_qry")
set rs = qd.openrecordset()

if rs(0) > 0 then
' do whatever you want here
end if

' clean up
set rs = nothing
set qd = nothing
set db = nothing

Using rs(0) means read the value of the first column in the returned
recordset.

You'll want to add a Error handler to this routine.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSULiXIechKqOuFEgEQIMIwCg1hdUPBY4Vsi23nf1iU2K26tTnA8AmQEx
MhYGRLJQqDLT2g2wKevX1jnJ
=x+x9
-----END PGP SIGNATURE-----