tonywilliams
Technical User
I have a calculated control in the detail section of a report. The calculation is
=[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]= [txtqtr1]"))
The calculated control is called prevqtramount. In a footer I want to calculate the sum
of this and have used =Sum([prevqtramount]) but nothing shows. Ive tried repeating the calculation and used
=Sum([txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]= [txtqtr1]")))
but that doesn't work either.
The field txtDomfacsoleqtr is a field in my table and holds year to date data for a number of companies which they supply on a quarterly basis.
The table is called tblmaintabs.
The txtmonthlabel is also a field in my table and is the date of the current quarter, eg March 2004
The txtqtr1 is a calculated control which calculates the previous quarter date using =DateAdd("m",-3,[txtmonthlabel])
The report I'm trying to produce does two things.
Firstly I want to compare the data for the current YTD with the previous quarter's YTD. I was using the DLookup expression to find the data for the previous quarter.
I then wanted to calculate the difference between the two to arrive at the quarter's data in other words Current YTD minus PrevQuarter's
YTD=Quarterly change. I was trying to use the current YTD data minus my DLookup expression to get this.
Having got these figures in the detail section I then wanted to total bothlots of data to arrive at the total figures for all the companies.
However my DLookup expression only uses one value for all companies! It looks like it uses the first calculation for the first
company and doesn't change when the company changes???
Can anyone point me in the right direction because I think I'm going about the wrong way.
Sorry this is so long but I've been at this for 3 days now and I'm getting nowhere.
Thanks
Tony
=[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]= [txtqtr1]"))
The calculated control is called prevqtramount. In a footer I want to calculate the sum
of this and have used =Sum([prevqtramount]) but nothing shows. Ive tried repeating the calculation and used
=Sum([txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]= [txtqtr1]")))
but that doesn't work either.
The field txtDomfacsoleqtr is a field in my table and holds year to date data for a number of companies which they supply on a quarterly basis.
The table is called tblmaintabs.
The txtmonthlabel is also a field in my table and is the date of the current quarter, eg March 2004
The txtqtr1 is a calculated control which calculates the previous quarter date using =DateAdd("m",-3,[txtmonthlabel])
The report I'm trying to produce does two things.
Firstly I want to compare the data for the current YTD with the previous quarter's YTD. I was using the DLookup expression to find the data for the previous quarter.
I then wanted to calculate the difference between the two to arrive at the quarter's data in other words Current YTD minus PrevQuarter's
YTD=Quarterly change. I was trying to use the current YTD data minus my DLookup expression to get this.
Having got these figures in the detail section I then wanted to total bothlots of data to arrive at the total figures for all the companies.
However my DLookup expression only uses one value for all companies! It looks like it uses the first calculation for the first
company and doesn't change when the company changes???
Can anyone point me in the right direction because I think I'm going about the wrong way.
Sorry this is so long but I've been at this for 3 days now and I'm getting nowhere.
Thanks
Tony
