By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

DLookup a field based on a parameter

beacon
100+
P: 579
[Access 2003]

I have a report that is pulling data from a query. The query gets sent a parameter from a form. The information on the form is correct, but my summary section is incorrect.

The reason the summary is incorrect is because of the expression used to summarize. I'm using DLookup to find totals in a separate query.

Here's an example of my DLookup now:
Expand|Select|Wrap|Line Numbers
  1. =DLookUp("CountOfStartingLocation","qryDestinationTotal","StartingLocation='Admissions'")
  2.  
This is great if I don't search by program, but ultimately that's what I want to do. The program is the parameter that's passed to the query initially. In order for me to pull the program parameter from this alternate totals query, do I need to add on to my DLookup?

I've added Program to the list of fields in the query and it works if I run the query...now I just need to get the summary fields to coincide.

Thanks for the help,
beacon
Dec 15 '08 #1
Share this Question
Share on Google+
4 Replies


beacon
100+
P: 579
I've attached a zip of the mdb. There are a couple of reports that have ByProgram in the title. These are the ones that need the parameter that's passed in to manipulate the summary fields at the end of the report.

Any help is greatly appreciated.

- beacon
Attached Files
File Type: zip Transport Service Tracking DB.zip (1.02 MB, 91 views)
Dec 15 '08 #2

100+
P: 166
Hi Beacon,
I do not think you can pass a parameter to a DLOOKUP...
Dec 15 '08 #3

Expert Mod 2.5K+
P: 2,545
csolomon's reply is correct - there is no way to supply a query parameter value as an argument to DLookup (or indeed to any of the other domain aggregate functions built in to Access - DSum, DCount and so on).

The simplest solution is to save a version of the query concerned without the reference to the form control you are filtering on, and use that with the Dlookup instead.

-Stewart
Dec 16 '08 #4

beacon
100+
P: 579
Okay...I gave up on DLookup and actually had some success with an alternate method on one report, but it's giving me trouble on another. It does the calculation after the parameter is accepted by the report, so I thought it would be okay to still include it within this thread.

Anyway, I have set a grouping level for a field on a query called [EndingLocation]. [EndingLocation] has string values like "Admissions", "Dental", and "Recreation." I entered the expression below, but I'm not returning any results, even though I know I have one entry for "Admissions."

Expand|Select|Wrap|Line Numbers
  1. 'Find the group labeled "Admissions" and total the number of records
  2. 'If the "Admissions" group doesn't exist, put nothing
  3.  
  4. =iif([EndingLocation]="Admissions", Sum([CountOfEndingLocation]), "")
  5.  
This isn't working for me for some reason although it worked on a similar report that was grouped by a formatted text box with a different name.

Any ideas?
Dec 16 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.