I have the following form:
The main form has the (blacked out) combo box that lets me specify a variable (63 options) that is applied to the subform control "sfmActivityCB". This allows me to evaluate data for each combo variable on the fly. Within the footer of the main form (frmActivityCB) I have placed unbound text boxes for which I would like to display select data derived from the AvgIC50 [IC50 (nM)] field of "sfmActvityCB". These data are then referenced using another text box in the main form as shown. As you (can't) see, the count function was used to return the data for "Assays tested":
That's the easy part. But then it gets tricky because for the remaining evaluations I need to exclude those data with (exact) numerical values of either 10,000, 3,162 or 1,000. As I understand, the DMax and DCount functions cannot be applied to a subform, and in my case this may be particularly true because I have built the requisite queries within the form rather than as stand-alone entities. So, to calculate the data for "Assays Inhibited" I used a set of nested IIF expressions as follows:
Code:
Count(IIf([AvgIC50]<>10000,IIf([AvgIC50]<>1000,IIf([AvgIC50]<>3162,1,Null),Null),Null))
This works great, though it feels clumsy. Then comes the next hurdle that I have been unable to solve. I want to display the max value for [AvgIC50] in the text box "Highest IC50", but must filter out the same three exact values first (10,000, 3,162 and 1,000). I cannot visualize a way to do this using the Max expression. Any ideas from the clever folks using this forum? If there is an easier way to do all of this, please let me know. I can post the database, but will need to do some work on it because it contains a fair amount of sensitive/proprietary information.
Thanks in advance for helping.