By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,205 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

HELP! I cant understand my DLookup problem!

P: n/a
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 Microsoft Access group for which I apologise
for cross posting in advance, but I seem to have problems seeing my posts on
the Microsoft newsgroups so I'm trying this one as well. Thanks in
anticipation
Tony
Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.