Max Function in Row Source Coding

[MJ]

I am having a minor problem with some coding I have for the Row Source in a
database. The current code is based on filtering the dropdown list an the
last month's data:

SELECT tblA.REVCENTER, tblA.FACILITY, Sum(tblA.TOTAL_AMT) AS SumOfTOTAL_AMT
FROM tblA
WHERE (((tblA.MONTH)=DateSerial(Year(Date()),Month(Date())-1,1))) GROUP BY
tblA.REVCENTER, tblA.FACILITY HAVING (((tblA.REVCENTER) Is Not Null) AND
((tblA.FACILITY) Not Like "W") AND ((Sum(tblA.TOTAL_AMT))>0)) ORDER BY
tblA.REVCENTER;

TblA.Month SystemDate Results
10.2008 11.01.2008 Displays list for 10.2008 correctly
10.2008 11.30.2008 Displays list for 10.2008 correctly
10.2008 12.01.2008 Dropdown List is EMPTY

The DateSerial works well as long as the the TblA.Month is the previous
month when the User accesses it. When the next monthly update has not
happened yet, say on the first of the next month (see the list above), the
dropdown list goes blank.

What I would like to display in the dropdown list is for the Last Month
updated. I thought of using the Max() function instead of DateSerial but I
an error:

Cannot have aggregate function in WHERE clause (tblA.MONTH=Max(tblA.Month)).

.... WHERE ((TblA.MONTH)=Max(tblA.MONTH)) ... ;

Does anyone have a good idea how I can do this simply in the Row Source?

Thank you in advance for your time and assistance,

 

[Douglas J. Steele]

Try using DMax("[Month]", "tblA")

Note that you really should rename your Month field. Month is a reserved
word, and you should never use reserved words for your own purposes. For a
comprehensive list of names to avoid (as well as a link to a free utility
that will check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

 

[MJ]

Doug, your suggestion worked great!

As for your observation about the reserved word, I agree completely, my only
defense is that I enherited this database and have not had the time to
rewrite and clean house on it. I will definitely do that at my first
opportunity.

Thanks again for the input!

--

MJ

Try using DMax("[Month]", "tblA")

Note that you really should rename your Month field. Month is a reserved
word, and you should never use reserved words for your own purposes. For a
comprehensive list of names to avoid (as well as a link to a free utility
that will check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html