Dear all, I have problems selecting current maximum date for current month that has been keyed in for the items. I'm unable to select the most current date.

I have to list the items of current month out under the if condition loop, as each item may hold 2 dates for the mth, so i have to select the most current mth entry date.


Dim maxM
query_maxM = "SELECT MAX(MONTH(m_date)) AS MAXM FROM DatePart where MAX(MONTH(m_date))=MONTH(now)"
Set rs_maxM = Server.CreateObject("ADODB.Recordset")
Set rs_maxM = conn.Execute(query_maxM)
maxM = rs_maxM("MAXM")

if month(now)= MAX(MONTH(maxM)) then


I actually have an error on the highlighted line:
Cannot have aggregate function in WHERE clause (month(MAX(m_date))=month(now())).
Will someone out there pls help me solve the query problem asap. Thanks so much!

Please try:

query_maxM = "SELECT MAX(MONTH(m_date)) AS MAXM FROM DatePart having MAX(MONTH(m_date))= MONTH(getDate())"

Om Prakash

Hi thanks om_prakash,

I managed to solve it using shorter query:

query_maxM = "SELECT MAX(m_date) AS MAXM FROM DatePart"
Set rs_maxM = Server.CreateObject("ADODB.Recordset")
Set rs_maxM = conn.Execute(query_maxM)
maxM = rs_maxM("MAXM")

if month(now) = month(maxM) then

Thanks for the help!