Compute filed with DLookUp in Query

[jfca283]

Hi
I'm designing a query. It's very simple.
I just need to compute something like:

Var: TT[YYZ for this month]-TT[YYZ for last month]

The table is called C1 and has the fields i'm interested
YYZ: Location
TT: number
mxx: date

So, i have for YYZ a list of places and for TT a list of values. MXX has the date of the record on the table. There is one record per date, which it means, there only one record for 1/1/1991 for the YYZ=2.
NF should calculate the difference between the the record on t against t-1.

I've tried with this
Var: YYZ-DLookUp("yyz","[C1]"," "[YYZ]=[YYZ]" AND "[mxx]=DateAdd("m", "-1", "[mxx]"))
But it fails.
Can you guide me?
Thanks a lot for your replys.

PS: i upload a xls sheet with the desired field, Var ,calculated.

 

[plog]

Debugging 101: Tear that thing apart until it works. You've obviously overreached going for broke all at once. Start with smallest piece and then work back to the whole thing.

Instead of this:

Var: YYZ-DLookUp("yyz","[C1]"," "[YYZ]=[YYZ]" AND "[mxx]=DateAdd("m", "-1", "[mxx]"))

Get this to work:

Var: DLookup("yyz", "[C1]")

Then add the YYZ criterion. Then add the date criterion. Then bring in YYZ to do math on. Get each step to work along the way.

 

[jfca283]

This is how it worked. Thanks for your guide.
Var: ([yyz]-DBúsq("[yyz]","C1_AS","cDbl([date])=" & CDoble(DateAdd("m","-1",[date])) & " AND [Area]=[Area]")