Using Dmax in Access VBA

If you want to use the Dmax in VBA.

Download Sample Database

Here is the code-

Sub use_dMAX()
Dim sumfield As String
Dim tblname As String
maxsales = "sales"
tblname = "sales_detail"
MsgBox DMax(maxsales, tblname, "[Rep Name] = 'a'"), vbOKOnly

' with query
Dim SQLresult As String
Dim rs As Recordset
SQLresult = "SELECT MAX([sales_detail].sales) AS Filtersales FROM [sales_detail] WHERE ([sales_detail].[rep name]='a')"
Set rs = CurrentDb.OpenRecordset(SQLresult)
MsgBox rs(0), vbInformation
End Sub