Exclude data if calculated value is null

[davanderbilt]

I have a report built on a query that combines data from 2 tables Body_Comp_Data and RMR_Data. There are 3 values for SBP and DBP and I have written code to average the SBP and DBP values for each table and have those values dumped into BC_AvgSBP, BC_AvgDBP, RMR_AvgSBP, and RMR_AvgDBP.

BCPeriod is 00-Base, 05-Mid, or 10-End. RMRPeriod is either Base or End. The SBP and DBP values are calculated at Base, Mid and End. Base and End values come from RMR_Data, and the Mid value comes from Body_Comp_Data.

//I use RMR_AvgSBP and RMR_AvgDBP for the base and end, but I wrote this function to call BC_AvgSBP and BC_AvgDBP for the mid values 
SBP: IIf([BCPeriod]="05-Mid",[BC_AvgSBP],[RMR_AvgSBP])
DBP: IIf([BCPeriod]="05-Mid",[BC_AvgDBP],[RMR_AvgDBP])

//these are the final expressions written in the query that returns the values that I need 
ExprSBP: IIf([RMRPeriod]="Base" And [BCPeriod]="00-Base",[RMR_AvgSBP],IIf([BCPeriod]="05-Mid",[SBP],IIf([RMRPeriod]="End" And [BCPeriod]="10-End",[RMR_AvgSBP])))

ExprDBP: IIf([RMRPeriod]="Base" And [BCPeriod]="00-Base",[RMR_AvgDBP],IIf([BCPeriod]="05-Mid",[DBP],IIf([RMRPeriod]="End" And [BCPeriod]="10-End",[RMR_AvgDBP])))

------------

The function works just as it should and when I run the query and outputs:

[

http://www.ebl.ku.edu/LTE/table.html

]

-------------

In the report, the Control Source for SBP is ExprSBP and the Control Source for DBP is ExprDBP.

The problem I face is with the part of the ExprSBP function...IIf([RMRPeriod]="End" And [BCPeriod]="10-End",[RMR_AvgDBP]. The expression returns no value for ExprSBP and ExprDBP for the RMRPeriod Base (and it shouldn't) but when I run the report, since that is the first row that comes up it doesn't show the data.

Is there some way I can exclude a null value that comes from from a calculated query value on the report?

 

[PaulBricker]

You could create a new query using the existing calculated query as the source, and then filter the fields in question using
Not Is Null
on the Criteria. Then base your report on the second query.


Paul

 

[davanderbilt]

Thank you Paul, that worked great!