Pass SQL Result to VBA Variable

[xljunkie]

Can someone please help me? I'm trying to count the distinct instances in a field and just store that number in a vba variable. I can create queries in Access and save this info in a table, and then DLOOKUP, but that seems too convoluted.

SELECT Count(FieldA) FROM (SELECT DISTINCT FieldA FROM tblExample)

 

[xljunkie]

Nevermind figured it out.

strSQL = "SELECT Count(FieldA) as A FROM (SELECT DISTINCT FieldA FROM tblExample)"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    Variable = rs!A

 

[apr pillai]

Here is an interesting Function for you to look at: The GetRows() Method of DAO Object. You can find the usage of this method here: GetRows Function and Exporting Data.

 

[xenou]

Also possible is using DCount (without the need for recordsets):

Sub foo()
    Dim x As Long
    x = DCount("*", "Table1")
    Debug.Print x
End Sub