How to count distinct records in Access table and display that value in Excel

I have a table in Access that I’m querying from Excel. I need help with the sql statement. First, I need to filter the table based on SampleType matched criteria indicated in the Where clause. There are only 3 options: “mel”, “col” or “ lun”. The ultimate goal is to pass the number of distinct records from the SampleID column for selected SampleType(s) to a variable and output it on a UserForm. SampleType column has duplicate values and need to count only distinct values. From what I’ve found online ‘COUNT(DISTINCT(‘ + column_name + ‘)) would work, but I’m not sure how to use it.

Thanks for your help in advance

Dim var6 As String
Dim var7 As String
Dim var8 As String
var6 = "mel"
var7 =  “lun”
var8 =  “col”
SQLwhere = "WHERE "

If CheckBox5 = True Then
SQLwhere = SQLwhere & "[Table1].[SampleType] LIKE '" & var6 & "%" & "' AND "
‘count a total district records in column SampleID. 
End If

If CheckBox6 = True Then
SQLwhere = SQLwhere & "[Table1].[SampleType] LIKE '" & var7 & "%" & "' AND "
End If

If CheckBox7 = True Then
SQLwhere = SQLwhere & "[Table1].[SampleType] LIKE '" & var & "%" & "' AND "
End If

StrSql = "SELECT * FROM [Table1] "

'Remove the last AND applicable
If SQLwhere = "WHERE " Then
    SQLwhere = ""
Else
    SQLwhere = Left(SQLwhere, Len(SQLwhere) - 5)
End If

StrSql = StrSql & SQLwhere

Answer

Your question is rather broad, so the answer is sort of generic. Pertinent to the Syntax of MS Access DISTINCT SQL, it could be like the following:

SELECT COUNT(*) AS UniqueRecordsCount FROM (SELECT DISTINCT [ColumnName] FROM [SampleTable]) AS T1

or simplified like:

SELECT COUNT(*) AS UniqueRecordsCount FROM (SELECT DISTINCT [ColumnName] FROM [SampleTable])

Corresponding to your additional question in comments, please see the following:

GetDistinctValue = rs("UniqueRecordsCount")

Hope this may help.