Dlookup Problem

[mrssevans]

I have a report that needs to show three consecutive years of data. I am using a Dlookup right now to get the previous years, but that is extremely slow. I am using a query to gather the data and have a report off of that. My dlookup is in the report. Any suggestions?

 

[Rich]

Create a query that gives you the previous three years and use that as the control source for a subreport, or use a query with all the data included, need some more details really

 

[mrssevans]

I have the data in a union query and then summed in another query. The way I have to structure the report is as such:

month
Item: 2001 2002 2003
data summed data ...


The problem is that I don't have a Field ID that I can realy use to join the tables if I split them out in multiple queries. It really works great in an excel pivot table and I would be willing to use that if I knew it would update and refresh correctly. Any suggestions?

 

[Rich]

Where does 2001,2002, etc come from, is this from one field?

 

[mrssevans]

Yes, All months are in one field, all dates in another field and there are about 4 summed fields.

 

[mrssevans]

No, but I will make that my next move. Thanks, Sam

 

[mrssevans]

On this subject, is there away to use multiple criteria in a dlookup? Here is what I have now:
=DLookUp("[ISEOH]","[SID'S 2]","([t]=" & [t]-1)
What I am doing is looking up the previous months total in Sid's (union query) when the month (t) equals the previous month. I also need it to equal the yard number. Something like
=DLookUp("[ISEOH]","[SID'S 2]","([t]=" & [t]-1 and [IS_YD]="&[IS_YD])

Can anyone help?

 

[mrssevans]

I used that formula, but I keep getting an error message. I am pulling this inforamtion out of a union query. The t is a datepart formula and the isyd is a number. Any suggestions?

 

[mrssevans]

Well, it's in a report that is populated by a union query. I double checked the names several times and that wasn't the problem. On the report it just says Error#.

 

[mrssevans]

No it didn't. If I split the union query so that it is just a select query and only use part of the dlookup formula it works fine. Could it possibly be the union query messing things up? is there another way I get data from the previous month?