# Thread: Compute filed with DLookUp in Query

1. Registered User
Join Date
Jun 2014
Posts
2

## Unanswered: Compute filed with DLookUp in Query

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?

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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
so holding the 'there is only one row per date' Id try a QUERY something like

Code:
```SELECT A.TT, B.TT, A.TT-B.TT as NF FROM C1 AS A
LEFT JOIN C1 AS B ON ((A.MMX = B.MMX-1) AND (A.XYZ = B.XYZ))```
the aliases (A. & B. are required as you are using the same table twice
ideally you should use the MS access/VBA datetime functions not the crude -1 appraoch I have here

the join will fail if you have used NOW to set the MMX, or expressly used a time as part of the date setting

3. Registered User
Join Date
Jun 2014
Posts
2
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]")

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•