DLookup Problem on Report

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
bkthomAsked:
Who is Participating?
 
IrogSintaCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
IrogSintaCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
IrogSintaCommented:
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
 
bkthomAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
better if you will upload a copy of the db.
0
 
IrogSintaCommented:
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
 
bkthomAuthor Commented:
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
 
IrogSintaCommented:
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
 
bkthomAuthor Commented:
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
 
bkthomAuthor Commented:
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
 
IrogSintaCommented:
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
 
bkthomAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.