dlookup in report - bad behavior (1 Viewer)

theseus

Registered User.
Local time
Today, 12:13
Joined
Aug 6, 2018
Messages
32
I am using dlookup in a report to pull the name of a facility using the cost center that was entered in a form. This is creating some weird consequences.

When the report is displayed in report view, it takes about 10 seconds to look up the name of the facility and to then show the other calculations on the report. It will also create the same delay if I scroll away from the data and back.

However, if I tell Access to show the report in print preview, there is absolutely no delay. I would love to hear any ideas about what is going on. I would prefer to not show the report in print preview, but it is an acceptable work around.


Thanks!
 

Ranman256

Well-known member
Local time
Today, 12:13
Joined
Apr 9, 2015
Messages
4,339
Don't use Dlookup in reports.
Put EVERYTHING in the query.
The query IS the Dlookup. (Using joins)
 

theseus

Registered User.
Local time
Today, 12:13
Joined
Aug 6, 2018
Messages
32
Don't use Dlookup in reports.
Put EVERYTHING in the query.
The query IS the Dlookup. (Using joins)
Since the report is based on a crosstab query, that is why I opted to cheat and put the label using dlookup.
 

plog

Banishment Pending
Local time
Today, 11:13
Joined
May 11, 2011
Messages
11,611
A report based on a cross-tab is a bad idea. because a corss tab essentially has dynamic columns. Expected ones may be missing and unexpected ones can show up and not make their way to the report.

Suppose you did a cross-tab query for production on days of the work week (Monday-Friday). Everything works fine until you hit a holiday and you were shut down on a Wednesday. You run the report and it won't generate anything because it expects a Wednesday value, but none is present in the underlying query--without any Wdenesday data, the Wednesday column doesn't even show up in the cross-tab.

Suppose further that unbeknowst to you a team came in over the weekend and worked. When you generate that report their production will not show up because it occured on days you hadn't explicitly put on the report.

With all that said, there was still no reason to "cheat" with a DLookup. You could have made another query, using the cross-tab as a data source and the Dlookup data source in there as well to get all the data you needed for your report. Of course that new query would be suspectible to the issues detailed above for your report.
 

theseus

Registered User.
Local time
Today, 12:13
Joined
Aug 6, 2018
Messages
32
With all that said, there was still no reason to "cheat" with a DLookup. You could have made another query, using the cross-tab as a data source and the Dlookup data source in there as well to get all the data you needed for your report. Of course that new query would be susceptible to the issues detailed above for your report.


Ultimately this is what I did. I created a new query with the crosstab and the other table with the institution name in it, with a left join between them.


As for the issues with the crosstab query, given the nature of my database and the type of data they are asking me to pull, it is not vulnerable to those problems yet. (That just means I haven't broken it yet.:rolleyes: I'm trying.)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:13
Joined
Feb 19, 2002
Messages
42,970
To answer your initial question:
The PrintPreview is a static view of the report. The query runs once when the report is opened and does not get updated as you scroll. If you print from the prieview, the query runs a second time.

The PrintView is similar to a form since it supports events for controls. That means that the query needs to be "live" and potentially update as you scroll.

But the Join is the correct solution regardless. DLookup()s can almost always be replaced by left joins. The only time you would not be able to do this is if the query is the recordsource for a form and you want the form to be updateable. A query that joins to a non-updateable table or query becomes not updateable making the form not updateable. A typical situation would be if you want to show a customer's outstanding balance on each order. That would entail joining to a totals query which would make the order form not updateable so in that case you would need to use DSum() or DLookup()
 

Users who are viewing this thread

Top Bottom