DLookup Problem

[Geoff Codd]

Hi there,

I could really do with someones help with the following, as I just can't get it to work.

Target Consumption: Dlookup("[ActualConsumption]","EACSTORE","[BatchDate] = DateAdd("yyyy",-1,[BatchDate])")

Thanks
Geoff

 

[Alexandre]

You've got problems with quotes, but first of all the DLookup criteria doesn't make sense. It won't ever return any result. What do you want to achieve and what is the selection criteria you want to use (in plain text) ?

 

[Geoff Codd]

Here is a little more info,

I have a query which has 2 fields, BatchDate and ActualConsumption. What I need to do is add a 3rd field to display the TargetConsumption, which is the ActualConsumption 12 Months Beforehand

Example

BatchDate 31/05/02
Actual Consumption 10000
TargetConsumption 9500 (The ActualConsumption for 31/05/01)

Any help greatly appreciated

Thanks
Geoff

 

[Fizzio]

This may solve your quotes problem,

Dlookup("[ActualConsumption]","EACSTORE","[BatchDate] = DateAdd(" & """y""" & ",-1,[BatchDate])")

but you must make sure thay there is a value with a date exactly 1 year ago and I think it is a single y rather than yyyy. (I cannot check this though)

 

[Geoff Codd]

thanks for your post, but i'm still not having any joy.

any other ideas
thanks
geoff

 

[David R]

Your problem is that the DateAdd is inside the quotes, so it is taken literally and confuses Access to no end. Try:

Dlookup("[ActualConsumption]","EACSTORE","[BatchDate] = " & DateAdd("yyyy",-1,[BatchDate]) )

Single "y" will subtract a day, paradoxically.

 

[Alexandre]

My understanding of what you are trying to do makes me think that your DLookup won't serve your purpose.
Create your query with your two fields.
In a 3rd column, tape in:
TargetConsumption: DateAdd("yyyy",-1,[BatchDate])
(May be you will have to replace the commas with semi-periods in the above text)

Your Dlookup won't give you any result since it is supposed to return the first value of ActualConsumption corresponding to your criteria. Now your criteria is always false: a date can never be equal to the same date one year before!

 

[Fizzio]

I've tried all ways unsuccessfully to get the DLookup to work in the query. Have you tried using the DLookup on the form/report in an unbound control to display the target consumption?

 

[Geoff Codd]

I've got it to work but not using dlookup I created a query showing the current years consumption and added a field with Target Date, I then created a second query with the previous years data. I then created a third query made up of the first 2 queries and added a join between the Target dates.

It's not the way I wanted to do it but at least it works.

Thanks evryone for your help
Geoff