Using DMax() in VBA on a query and getting incorrect result

[BRZ-Ryan]

I have a query that sums data from a table. I am then using DMax() from VBA on this query but it is not giving me the max value from the results of the query. When I run this query in Access and I review the resulting values, it shows me a number in the hundreds and many other higher numbers, while DMax() returns 9. Does DMax() find the max of the individual values that were used to calculate the sum? I would think it would give me the max value in the result of the query. Please help.

Thank you

[June7]

Should pull the max value of the aggregate calcs.

Post the SQL statement of the query and the VBA code.

[BRZ-Ryan]

Should pull the max value of the aggregate calcs.

Post the SQL statement of the query and the VBA code.

Will post shortly. I cannot post the tables that feed it, is that ok?

[BRZ-Ryan]

Ok, so I fixed it, but still not sure why I need a work around in this scenario. I noticed in the query results, this column was left aligned which IIRC indicates its text and not numeric so it was probably giving me the max ascii value? The fields in this table are all set to numbers so it should be giving me numbers in the query? Doing a + 0 gives me what I want.

Sum(MonthSum) + 0 As AvgNum

Pay not attention to the 'AvgNum' naming convention. This is shared with things that use averages.

Public Function RangeQueryAttendanceSum(ByVal strField As String) As String
RangeQueryAttendanceSum = _
    "SELECT tblAgents.ID As AgentID, Sum(MonthSum) + 0 As AvgNum, " & lstMonths.ItemsSelected.Count & " As NumRecords" & vbCrLf & _
    "FROM tblAgents" & vbCrLf & _
        "LEFT JOIN (" & vbCrLf & _
            "SELECT tblAttendance.AgentID As AgentID, Sum(" & strField & ") As MonthSum, Count(*) As NumErrorRecords" & vbCrLf & _
            "FROM tblAttendance" & vbCrLf & _
            "WHERE tblAttendance.RepType=" & cmbRepType & " AND " & BuildMonthCondition() & vbCrLf & _
            "GROUP BY tblAttendance.AgentID" & vbCrLf & _
        ") As GetAttendance" & vbCrLf & _
        "ON tblAgents.ID=GetAttendance.AgentID" & vbCrLf & _
    "WHERE tblAgents.RepType=" & cmbRepType & vbCrLf & _
    "GROUP BY tblAgents.ID;"
End Function

[June7]

I also do not understand why the data was returning as text.

Regardless, the max returned should have been the actual text, not an ASCII.

However, as long as it works, guess that is what's important.