Results of DLookup not showing in Report View, but are visible in Print Preview

[gwunta]

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.

 

[DavidAtWork]

perhaps you could run some kind of make-table query that will retrieve the values required for the report into a temp table, run this action as a step before opening the report which can be based on a simpler query driven by the temp table data.

David

 

[maosc]

Please check the control property on report "Display When" in format tab. it should be Always instead of "Print Only". if also not showing the value, then there can be null value in both of them.

 

[gwunta]

Thanks guys. Maosc I checked the properties, the Display When property is already set to Always so that is not the cause. DavidAtWork, I would really like to do that, but essentially its still building a query and because the data sets are so disparate, this is not a viable option. I would end up with a huge data set with many fields containing null values in many of the fields. Im guessing from these responses that there is nothing wrong with my syntax so this one has me stumped. Right now it seems as though its a bug or shortcoming of Access 2007

 

[JHB]

Why not use the "Print Preview" instead of "Report View"?

 

[adh46]

Did anyone find a solution to this?

 

[The_Doc_Man]

adh46, obviously not in this thread, which was six yeas ago. If you need an answer, I suggest you start your own thread and just reference this thread.

I suspect it has to do with whether the event you are using will actually fire. You should be aware that if you weren't going to print the report (because you didn't enter Print Preview view mode), the OnPrint events don't fire.