T
Tony Williams
I have posted a number of posts in the Access newsgroups concerning my
problem with DLookup. I have had a number of the experts with helpful
suggestions but I still can't get it to work! This is what I am trying to
do:
I have a table called tblmaintabs which holds year to date data for a
number of companies which they supply on a quarterly basis.
The field txtDomfacsoleqtr is one of the fields that holds part of this
data. The txtmonthlabel is also a field in my table and is the date of the
current
quarter, eg March 2004
The report I'm trying to produce is based on my table and a query which
calculates the previous quater date and should do two things.
The query is linked to my table on txtmonthlabel and calculates the previous
quarter using this expression.
DateAdd("m",-3,[txtmonthlabel]) and is txtqtr1 in my Dlookup formula below.
Firstly I want to compare the data for the current YTD with the previous
quarter's YTD. I was using this DLookup expression to find the data for the
previous quarter.
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]= [txtqtr1]")
This value I want to call prevqtrvalue
However this formula calculates the value of txtDomfacsoleqtr for the first
company, which is mathematically correct, and shows the same value for all
the other companies.
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. using something like this
Quarterly change= [txtDomfacsoleqtr]-[prevqtrvalue]
I have also tried using my full DLookup expression instead of preqtrvalue.
Although the calculation doesn't create any Access error messages, the
caculation just deducts the same value of prevqtrvalue for each company
rather than calculating the value of prevqtrvalue relative to that company.
Having got these figures in the detail section I then wanted to total both
lots of data to arrive at the total figures for all the companies.
Am I going about this the wrong way? I've tried all sorts of things over the
last three days and don't seem to be getting anywhere. Can someone point me
in the right direction? I'm happy to learn I just need guidance on where to
go, I'm getting desperate!.
I have also posted this in the comp.databases.ms-access group for which I
apologise for cross posting in advance, but I seem to have problems seeing
my posts on the Microsoft newsgroups. Thanks in anticipation
Tony
problem with DLookup. I have had a number of the experts with helpful
suggestions but I still can't get it to work! This is what I am trying to
do:
I have a table called tblmaintabs which holds year to date data for a
number of companies which they supply on a quarterly basis.
The field txtDomfacsoleqtr is one of the fields that holds part of this
data. The txtmonthlabel is also a field in my table and is the date of the
current
quarter, eg March 2004
The report I'm trying to produce is based on my table and a query which
calculates the previous quater date and should do two things.
The query is linked to my table on txtmonthlabel and calculates the previous
quarter using this expression.
DateAdd("m",-3,[txtmonthlabel]) and is txtqtr1 in my Dlookup formula below.
Firstly I want to compare the data for the current YTD with the previous
quarter's YTD. I was using this DLookup expression to find the data for the
previous quarter.
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]= [txtqtr1]")
This value I want to call prevqtrvalue
However this formula calculates the value of txtDomfacsoleqtr for the first
company, which is mathematically correct, and shows the same value for all
the other companies.
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. using something like this
Quarterly change= [txtDomfacsoleqtr]-[prevqtrvalue]
I have also tried using my full DLookup expression instead of preqtrvalue.
Although the calculation doesn't create any Access error messages, the
caculation just deducts the same value of prevqtrvalue for each company
rather than calculating the value of prevqtrvalue relative to that company.
Having got these figures in the detail section I then wanted to total both
lots of data to arrive at the total figures for all the companies.
Am I going about this the wrong way? I've tried all sorts of things over the
last three days and don't seem to be getting anywhere. Can someone point me
in the right direction? I'm happy to learn I just need guidance on where to
go, I'm getting desperate!.
I have also posted this in the comp.databases.ms-access group for which I
apologise for cross posting in advance, but I seem to have problems seeing
my posts on the Microsoft newsgroups. Thanks in anticipation
Tony