[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DLookup Problem on Report

Posted on 2012-09-12
13
Medium Priority
?
782 Views
Last Modified: 2012-09-17
I have a Report (Access 2010) that I am using a DLookup function on a query to pull monthly data from  table that I am appending data (surprise) also monthly.  I have not been able to pull any data other than "#Error", "?Name" , "0" or a Null.  I am going absolutely INSANE.  I have done DLookups before but have never run into so many errors and issues.  I know that you have to follow the DLookup syntax carefully, be careful of data format types, and spaces, quotation marks, etc . . . I thought that I had everything lined up and what should be a semi-easy report has turned into a major NIGHTMARE.  I have spent the past four hours trying everything that I know but only receiving the gift of endless frustration.  So, I am asking if anyone has had this issue and how they resolved it or if anyone can help if they understand what I am doing wrong.  

For some background, I have attached three screen shots (w/o data) of the report template, (design view) of table & query, so there is some context.  

If you look at the report you can see that I am (trying) to pull specific fields from each of the three months using DLookup.  The Report Record Source is a query = qryProjManager.  

On the Report in the Control Source for each month I am following the DLookup("Expr", "Domain", "Criteria") syntax for each of the five fields under each month.  An example of my DLookup for the AROver75 field for September:   =DLookUp("[AROver75]","[qry_Project Manager_HISTORY]","[qry_Project Manager_HISTORY]![CurrMonthYr] = '092012'").

The [CurrMonthYr] field is a calculated field created during my monthly append query:  CurrMonthYr:  Format([Data Date], "mmyyyy") which AS A String should work in my Criteria piece of DLookup.  

The last piece is that each of the five fields (for each month) is a number and has been cast? as a Long Integer by CLng() for each numerical field.  Each of the five text boxes (per month) is formatted as a "Standard" number.

I want to thank you in advance for any help you might be able to share.  I would really appreciate it.

Thanks again,

Brett
ScreenSht-rptProjMgr.docx
ScreenSht-tblProjManager-HISTORY.docx
ScreenSht-SQL-for-qryProjMgr-HIS.docx
0
Comment
Question by:bkthom
  • 6
  • 5
  • 2
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38393528
try this, change

=DLookUp("[AROver75]","[qry_Project Manager_HISTORY]","[qry_Project Manager_HISTORY]![CurrMonthYr] = '092012'").


with

=DLookUp("[SumOfAROver75]","[qry_Project Manager_HISTORY]","[CurrMonthYr] = '092012'")

or

=DLookUp("[SumOfAROver75]","qry_Project Manager_HISTORY","[CurrMonthYr] = '092012'").
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38393543
Rather than use DLookups, it would be much better to use a subreport.
Create a copy of your report and call it rptProjMgr_sr (or use whatever naming convention you like).  Then, in this subreport, delete all the objects except those you see here.  Of course you would need to move everything to the upper left corner in order to resize your report:
subreportThen just insert your 3 bound textboxes in the Detail section.
SumOfTotalAR
SumOfUnbilledOver45
SumOfUnbilledOver30
In your report footer section, add 3 unbound textboxes with the following ControlSources:
=Sum(SumOfTotalAR)
=Sum(SumOfUnbilledOver45)
=Sum(SumOfUnbilledOver30)
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38393551
Here's Part 2
Now in the detail section of your main report, replace the other objects with 3 subreports controls that all point to the same subreport.  You can just drag the subreport to your main report 3 times.  Main ReportThen you can just set the Parent/Child dependencies of the subreport.  Use CurrMonthYr for the Child and the name of each yellow textbox above each subreport for the Parent.  I'm assuming each one would have something like 072012, 082012, 092012.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:bkthom
ID: 38397315
capricorn1 and IrogSinta,

First of all thank you VERY much for your assistance.  I tried your suggestions and unfortunately I could net get them to work.  

capricorn1 - when I tried your suggestions when I ran the report nothing appeared.  I just got null data.  I really hate these damn DLookups!

IrogSinta - I tried the subreports.  I did not relly send a instructive screenshot of what my report is trying to look like (with the two groupings - Division and by ProjectManger).  So, I enclosed a screenshot that shows a clearer layout/grouping of the report.  I did do what you suggested but when I ran the report each Project Manager had both the title labels/boxes and data for every project for every Project Manager (which was close to 220 pages long).  So I took off the label/title box portion of the subreport and just kept the five fields.  I also added the ProjectMgr field to the subreport so it was all a single line with six fields.  I could not use the yellow (control source) text boxes with the [Data Date] field for the parent/child relationship.  I could only use one of the existing fields, so I used CurrMonthYr.

I ran the report  and it populated!  YEAH!! I put the subreport with the =sum([fields - 6 of them]) in the ProjMgr footer.  I got the consolidated amounts (which I wanted for each Project Manager) along with their name, again in the first (left subreport).  So I put in the other two subreports, but made the ProjMgr field VISIBLE = NO, and kept the other five fields as is.

I ran the report again and it populated BUT unfortunately it populated each report with the same data as was in the first box.  It just pulled the consolidated data for the (non-visible) project manager for the initial month (9/10/12).  But hey, something did populate in the report, so it was a positive step forward.  

So then I tried to get the other subreports to populate with the other months' data.  That did not work too well.  It didn't work at all.  So I tried creating three queries, then three tables, then three subreports (one for each month) to run in the report.  That didn't work.  All I got for the other two queries were endless Parameter Msgbox Queries asking me to enter in amounts for all of the various fields in the report.  So I just clicked "OK" and got to the end each time without any data in the report (subreports).  

I really want this to work and hope that you can help me.  I don't know . . . . how but if there is a way to double the points I can give, let me know and I will happily give them to you if I can get this report working.  I thought this would be difficult but  NOT this difficult!  I have spent over one and a half weeks just working on this section of the report and slowly driving myself and those around me completely insane.  So let me know if you know how to get double points.

So besides what I have already said, I am wondering:

1)  If there is a way to do a grouping giving my  "Group, Sort and Total" section at the bottom of the report (see attached).  I got all of the ProjectMgrs to populate and their consolidated data but I could not break them out into Divisions.  How do you do that type of grouping in a subreport.  Do you do a "Sort, Group and Total" to mimick what you have in the main report?  How does that work in the main report, do you just take out all of the headers and footers and keep them in the subreport instead?

2)  How do I pull the correct month's data for each subreport.  Is it possible to use the yellow (control source) text box in the parent/child relationship  that yellow box has the [Data Date] field and I could tie that to the Data Date in the subreport (would I need to include that field - the "Data Date" field somewhere in each subreport OR do you have to stick with fields instead of "control objects" like these yellow boxes each with the correct month (txtCurrMonth, txtLastMonth, txtTwoMonthsAgo), if I could use these boxes as the Parent for the subreports that would be OPTIMAL.  If you type in the "txtCurrMonth" into the specific fields in the subreport property boxes will it accept them?  I tried, but could only get the parent/child relationships using the subreport properties box (the two fields) to only accept a current field in the report.  How do you work around that?    Or do I have to do seperate queries or tables for each subreport, each query/table with a distinct month?

I am writing too much, you get the obstacles I am facing.  I am very close and DO VERY MUCH WANT TO THANK YOU for your assistance.  I hope that you will stick with me and let me know how I can reward you.  Please let me know if you have any additional questions and I hope the attached report makes more sense.  Again, many thanks in advance.

Regards,

Brett
ScrnSht-Report.docx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38397327
better if you will upload a copy of the db.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38397358
Your subreport has to mimic the sorting and grouping of the main report.  For your Parent/Child dependencies, add 3 invisible textboxes and use those for the Parent since your yellow textboxes are in a different format.  I'm assuming that your Yellow textboxes are calculated with something like these:
= Date()
= DateAdd("m",-1, Date())
= DateAdd("m",-2, Date())

Your hidden textboxes then should be
= Format(Date(),"mmyyy")
= Format(DateAdd("m",-1, Date()),"mmyyy")
= Format(DateAdd("m",-2, Date()),"mmyyy")

Now you said that you made the Proj manager field in the other subforms invisible, does that mean that you created more than one subform?  I meant for you to create a single subform and insert the same one into your main form 3 times.  

Just as Cap1 said, it would be much simpler if you could upload a stripped down version of your database with just the objects we need to see, less any privacy data.
0
 

Author Comment

by:bkthom
ID: 38397376
Hey you guys are great!!!

I hit submit and within a few minutes I had responses from both of you.  UNBELIEVABLE!!!

When you say a "stripped down" version of my db, can I just send out a db without data in the tables?  Along with (sub)reports and queries?  Will that work?

If so I will have to send it in the morning?  Will that work?  Let me know what you need.  And again MANY thanks in advance!

Brett
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38397385
Having some data would be preferable in order to test it.  Even a set of 5 records in order to see the grouping.   Just remove anything that would be private and you can just change the project manager names to fictional characters or something.
0
 

Author Comment

by:bkthom
ID: 38397456
IrogSinta -

Thanks for the response!

Sounds good.  I will send it off to you in the morning so I can get rid of the large amt of data and send you a sampling.  Thanks for letting me know what would will work for you.  Let me know how I can double up on points for you (I mean it).

Until tomorrow,

Regards,

Brett
0
 

Author Comment

by:bkthom
ID: 38399782
capricorn1 and IrogSinta,

Here is my database, I have been working with.  When you open it you can see the issues that I have been having with the subreports.  (I did not include the fields for DLookup because you can get a sense from the subreports and the information in the previous posts what I am trying to do).  But please let me know if you have any questions or need clarification.

Good luck and again . . . MANY, MANY thanks in advance for any assistance that you are able to provide.

Brett
StrippedDownDB.accdb
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38401163
Here you go.  Your database presented some difficulties since your groupings of Divisions and Project managers were not static.  I went on the assumption that within the past 3 months, you could have a new project manager, or you could lose one, or one could move to a different division.  

So when you open your report, a temp table is first populated with the names and divisions of all the project managers for the past 3 months with each month having the whole set of PMs.  This way the groupings on all 3 subforms would match.

Have fun with the final touches.
 :-)
StrippedDownDB.accdb
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38402046
I did give you incorrect information earlier so let me correct myself.
The main report will not have a record source and is used as a place holder for all the headings.
There are 2 subreports - one with the project managers listed.

The 2 subreports have the same recordsource, grouping, and sorting.
All 3 controls for subreports have a parent/child relationship with 3 hidden calculated textboxes on the main report
report
0
 

Author Closing Comment

by:bkthom
ID: 38406428
IrogSinta -

You are amazing!  I don't know how you can jump into a person's complex report and derive a solution.  I want to thank you SO VERY much for helping me to resolve this stumbling point in my report.  And, just as (if not more) importantly, to help me understand subreports and how to use them.  VERY HELPFUL.  I would have never tried using subreports without your help so THANK YOU.  Those subreports are strange little objects..

This issue has been resolved and again THANK YOU SO VERY MUCH FOR ALL OF YOUR ASSISTANCE . . . YOU ARE GREAT.

Now, I am running into another issue with calculated fields in the two subreports.  I am going to OPEN A NEW QUESTION (since it is a new issue) and ask for help in how I do calculated fields to run in the subreports for this report.  

Please look for it (I don't know how you search - by my user name)?  You should know what I am talking about in the question and I am sure there is an easy answer and another 500 points.  After I submit this I am going to post the new question.

Again . . . . MANY THANKS!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I came across an unsolved Outlook issue and here is my solution.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question