Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-25-2019, 09:27 PM   #1
SarahNorth
Newly Registered User
 
Join Date: Apr 2019
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
SarahNorth is on a distinguished road
DLookup based on Average, no result

I have created a report that has averages for some groupings. I then use DLookup to retrive marks assaigned to those specific averages from another table. The correct result only shows for the last record on the report. It is as if the result from =Avg() is calculated after the field with the DLookup function executes. If I use a field withouth a calculation to test my DLookup function everything works out fine.
How can I force one calculation to be completed before another or how to I get Access to execute the DLookup function after a second or even put that code behind a button.
I want to use the average in the report to look up the primary key in another table and then return what is in the field corresponding to that ID into a text field.
Help please

SarahNorth is offline   Reply With Quote
Old 05-25-2019, 11:03 PM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,011
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: DLookup based on Average, no result

I've read this several time but am struggling to understand how the different parts link together. Th fact that there have ben almost 20 views with no other replies may indicate I'm not alone.
Please try explaining again and add screenshots or attach your db. Zip first as you have less than ten posts.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
SarahNorth (05-26-2019)
Old 05-26-2019, 01:25 AM   #3
SarahNorth
Newly Registered User
 
Join Date: Apr 2019
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
SarahNorth is on a distinguished road
Re: DLookup based on Average, no result

OK I will try to explain better.
The first attachment shows the report and how the fields I want to show to display my DLookup result are empty and the second attachment shows my DLookup. It is as if the Average function on which the DLookup relys on doesn't get calculated until after the DLookup. Do I have to create a temporary table or can I use an event to force the report to populate the fields. Still, help please!
Attached Images
File Type: jpg Database.jpg (22.3 KB, 19 views)
File Type: jpg Database2.jpg (7.5 KB, 15 views)

SarahNorth is offline   Reply With Quote
Old 05-26-2019, 01:58 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,011
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: DLookup based on Average, no result

Yes I can see why it wouldn't work reliably.
You're trying to look up values using criteria that aren't stored data.
Suggest you use an aggregate query to calculate the averages.
Then base your DLookup on that query rather than just the table.
That way you know the averages are calculated first
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
SarahNorth (05-27-2019)
Old 05-27-2019, 05:30 AM   #5
SarahNorth
Newly Registered User
 
Join Date: Apr 2019
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
SarahNorth is on a distinguished road
Re: DLookup based on Average, no result

Hi Isladogs
Thank you very much that has worked
SarahNorth is offline   Reply With Quote
Old 05-27-2019, 07:56 AM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,011
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: DLookup based on Average, no result

Excellent. Good luck with the rest of your project.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Reply

Tags
calculations , dlookup access forms , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
dlookup put the result in form hamish mather Forms 5 01-07-2012 08:48 AM
Using result of a dlookup... cjmitton Modules & VBA 7 11-04-2011 06:06 AM
DLookup using another Dlookup result stu999 Queries 4 11-09-2010 07:02 AM
Dlookup is giving 0 result aman Modules & VBA 5 04-22-2009 04:42 AM
Incorrect result for an average DJBummy Queries 10 09-23-2006 06:28 PM




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


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

Featured Forum post


Sponsored Links


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