Question Count number of record with SQL result in MS Access

[pckong]

Hello all

I want to return the number of record with the SQL search in MS Access. I am using the following code, the expected result will be a value. But somehow, it does not give any result.

StrSQL = "SELECT COUNT(*) FROM table1 WHERE [Condition]='A' "

How do I return the count value with the search?

Thanks
Pete

 

[bob fitz]

Hello all

I want to return the number of record with the SQL search in MS Access. I am using the following code, the expected result will be a value. But somehow, it does not give any result.

StrSQL = "SELECT COUNT(*) FROM table1 WHERE [Condition]='A' "

How do I return the count value with the search?

Thanks
Pete

Try:

Dim i As Integer
i = DCount("*", "table1", "[Condition]='A'")

The code you posted does work, but it doesn't do what I think you expected. It actually assigns the SELECT statement to the variable called StrSQL

 

[pr2-eugin]

Hello Pete, In Access you use Recordset objects.. Similar code, but will need some modifications.. However in Access, you can use the native DCount function..

countVal = DCount("*", "tableName", "conditionField = 'A'")

 

[pckong]

Thank you Bob and Paul for the repply.
The above code works and it solves my problems!

Cheers