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]","[qr
hYr] = '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.