Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2018, 07:47 AM   #1
theseus
Newly Registered User
 
Join Date: Aug 2018
Posts: 19
Thanks: 1
Thanked 1 Time in 1 Post
theseus is on a distinguished road
dlookup in report - bad behavior

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!

theseus is offline   Reply With Quote
Old 08-15-2018, 07:49 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,123
Thanks: 0
Thanked 682 Times in 667 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: dlookup in report - bad behavior

Don't use Dlookup in reports.
Put EVERYTHING in the query.
The query IS the Dlookup. (Using joins)
Ranman256 is offline   Reply With Quote
Old 08-15-2018, 07:51 AM   #3
theseus
Newly Registered User
 
Join Date: Aug 2018
Posts: 19
Thanks: 1
Thanked 1 Time in 1 Post
theseus is on a distinguished road
Re: dlookup in report - bad behavior

Quote:
Originally Posted by Ranman256 View Post
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.

theseus is offline   Reply With Quote
Old 08-15-2018, 09:26 AM   #4
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,933
Thanks: 10
Thanked 2,147 Times in 2,102 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: dlookup in report - bad behavior

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.
plog is offline   Reply With Quote
Old 08-15-2018, 10:06 AM   #5
theseus
Newly Registered User
 
Join Date: Aug 2018
Posts: 19
Thanks: 1
Thanked 1 Time in 1 Post
theseus is on a distinguished road
Re: dlookup in report - bad behavior

Quote:
Originally Posted by plog View Post
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. I'm trying.)
theseus is offline   Reply With Quote
Old 08-15-2018, 10:43 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: dlookup in report - bad behavior

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()

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
theseus (08-16-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
strage report/subreport behavior... dmattarn Reports 26 07-30-2015 10:19 AM
[SOLVED] Sum DLookUp in Report Justin.ITPro Reports 0 07-24-2014 06:28 AM
DLookup in report Ankarn Reports 0 07-08-2008 11:23 PM
DLookup in Report CTQE General 1 03-24-2008 09:06 AM
Report DLookup? gpass Reports 1 04-10-2007 04:56 AM




All times are GMT -8. The time now is 10:46 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World