Hi guys, not sure sure if this is a shortcoming of Access or programmer error (most likely the latter).
I have a report with quite a few subreports in it. There are a number of calculated fields on the form, most of which use Dlookup to retrieve at least one of the figures required for the calculation. The Dlookup runs fine and the report opens but the calculated fields are devoid of data in Report view. When I switch to Print Preview view the fields are now populated. Below are two variations of the DLookup syntax I have used to try and alleviate this issue.
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[sub_AllHours].[Report].[8]
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[Reports]![rpt_AllFigures]![sub_AllHours]![8]
Note that the field [8] specified here is simply a month number and forms a column in the crosstab query for the corresponding query name.
Does anyone have any other suggestions? I would add the query referred to in the DLookup to the source query for the report but the source report's data is derived from a Crosstab query, which only accepts one data field (Access terms this as the value field.
I have a report with quite a few subreports in it. There are a number of calculated fields on the form, most of which use Dlookup to retrieve at least one of the figures required for the calculation. The Dlookup runs fine and the report opens but the calculated fields are devoid of data in Report view. When I switch to Print Preview view the fields are now populated. Below are two variations of the DLookup syntax I have used to try and alleviate this issue.
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[sub_AllHours].[Report].[8]
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[Reports]![rpt_AllFigures]![sub_AllHours]![8]
Note that the field [8] specified here is simply a month number and forms a column in the crosstab query for the corresponding query name.
Does anyone have any other suggestions? I would add the query referred to in the DLookup to the source query for the report but the source report's data is derived from a Crosstab query, which only accepts one data field (Access terms this as the value field.