Max Function in Row Source Coding MJ 12/12/2008 5:00:09 PM 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, -- MJ

Re: Max Function in Row Source Coding "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 12/12/2008 5:09:24 PM 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 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "MJ" <MJ[ at ]discussions.microsoft.com> wrote in message news:F721D725-FB42-48AB-9BD9-B1F3512760B0[ at ]microsoft.com... [Quoted Text] >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, > -- > > MJ

Re: Max Function in Row Source Coding MJ 12/12/2008 10:02:07 PM 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 "Douglas J. Steele" wrote: [Quoted Text] > 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 > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "MJ" <MJ[ at ]discussions.microsoft.com> wrote in message > news:F721D725-FB42-48AB-9BD9-B1F3512760B0[ at ]microsoft.com... > >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, > > -- > > > > MJ > > >