obtaining return value of count and using in vba

[slames]

Hi, I have a very simple sql statement:

Select Count(scMailTag)
From tblSubs
Where scMailTag = yes

I need to get the return value from this and store it to a variable so I can use it further. How do I get that return value?

Thanks in advance

Steph

 

[dhookom]

Dim lngMyCount As Long
lngMyCount = DCount("scMailTag","tblSubs","scMailTag = True&quot
This assumes scMailTag is a yes/no field.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bboffin]

This query returns a recordset with one row and one column so assign the result of the query to a recordset and the value of RS.Field(0) is what you want.

It might be better to phrase the sql query as:

Select Count(scMailTag) As MailCount
From tblSubs
Where scMailTag = yes

then you can refer to the return value by the column name "MailCount"