Results 1 to 3 of 3
  1. #1
    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?
    Thanks a lot for your replys.

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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    2
    This is how it worked. Thanks for your guide.
    Var: ([yyz]-DBsq("[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
  •