Count in Select Statement

[Elena]

I am trying to get a count of records in a table. Currently my code looks
like this:

strsql = "Select Count(*) From TempFleet where MO > " & intMo & " and YR =
" & Tyr
Set incount = CurrentDb.Execute(strsql)

When I do a debug.print on the code and run it a SQL version of the query it
works. I know that Current.Execute (strsql) doesn't work for a select
statement. I've tried other methods and none working.

Could someone tell me how to run fix this code?

Thanks in Advance!

 

[Ted Allen]

Hi Elena,

You could open a recordset based on your sql and then pull the value from
the first (and only) record. But, I think it would be much easier to use the
DCount() function. Something like:

inCount = DCount("*","TempFleet","MO > " & intMo & " and YR = " & Tyr)

HTH, Ted Allen

 

[Gerald Stanley]

Try

Dim rs As DAO.Recordset
strsql = "Select Count(*) As rowCountFrom TempFleet where MO > " & intMo &
" and YR = " & Tyr
Set rs = CurrentDb.OpenRecordset(strsql)
intCount = rs!rowCount
rs.Close
Set rs = nothing

Hope This Helps
Gerald Stanley MCSD