How to select the maximum value

[Crash_Pie]

Hi all

I have an expression in a query which is checking a list box on a form. If the list box is empty (is null), then I would like the query to search on the highest (maximum) value in the specified field (CourseFK), otherwise it will look for the value in the list box.

Expr1: IIf([Forms]![frmEditDeleteStudentDetails]![lstEditCourseFK] Is Null,[CourseFK]=Max([CourseFK]),[CourseFK]=[Forms]![frmEditDeleteStudentDetails]![lstEditCourseFK])

However it seems Access will not allow me to do this - I get an error about an aggregate function in a where clause.

Can someone please suggest to me how to get around this? Eg, is there an alternative way to search for the maximum (still needs to be determined dependant on the list box).

Thanks

 

[KenHigg]

Seems you may be able to squeeze a dmax() fuction in there somewhere....

ken

 

[Crash_Pie]

Thanks Ken

Worked a treat!