Difference between two rows / Dlookup syntax

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I need to calculate the difference between a field value
and the previous record. I tried using the Dlookup
function to create a field that displays both values on
the same row. Unfortunatly, the statement returns the
first value in all record.

The Dlookup method may not be the best solution, so I am
open to other suggestions. The rows are weekly data, with
one field named "Wk Ending". I created a field using the
dateadd function that returns the Wk Ending value less 7
days named "Last WE". All data is within the same query.
My Dlookup statement is as follows:

Calc Field: DLookUp("[Field1]","[Query1]"," '#[Wk Ending]#
=' & '#[LAST WE]#' ")

What am i doing wrong and is there a better way?
 
The function below picks up the previous row from the form, regardless of
how the form is filtered or sorted.

If that's no suitable, you may be able to use the Extended DLookup from:
http://allenbrowne.com/ser-42.html
which allows you to ask for the value of a related field and specify a sort
order.


Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
'Keywords: PriorRow PreviousValue
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function
 
Tom,

Using DLookup can be somewhat slow, if you have a large table, but is
an acceptable method.

Try the following:

Calc_Field: DLOOKUP("[Field1]", "Query1", "[WeekEnding] = #" &
dateadd("d", -7, [WeekEnding]) & "#")

You don't need to wrap the first date field in # signs.
If you created the [LastWE] field in this same query, it will not work
because you cannot refer to a computed column name within the query
that creates the computed column. If [LastWe] was actually created in
Query1, then that part of things should work. But if the reason you
created Query1 was to add the [LastWE] Field, then you can probably
replace Query1 in the DLOOKUP with the name of your table.

Another way to handle this would be to join two instances of your
table, something like:

SELECT t1.[WeekEnding]
, t1.Field1 as ThisWeek
, t2.Field1 as LastWeek
, t2.Field1 - t1.Field1 as Difference
FROM yourTable t1
LEFT JOIN yourTable t2
ON t1.WeekEnding = dateadd("d", 7, t2.Weekending)

The down side of this method is that you cannot actually see the query
in design view, since it uses a join that does not simply link two
fields.


--
HTH

Dale Fye


I need to calculate the difference between a field value
and the previous record. I tried using the Dlookup
function to create a field that displays both values on
the same row. Unfortunatly, the statement returns the
first value in all record.

The Dlookup method may not be the best solution, so I am
open to other suggestions. The rows are weekly data, with
one field named "Wk Ending". I created a field using the
dateadd function that returns the Wk Ending value less 7
days named "Last WE". All data is within the same query.
My Dlookup statement is as follows:

Calc Field: DLookUp("[Field1]","[Query1]"," '#[Wk Ending]#
=' & '#[LAST WE]#' ")

What am i doing wrong and is there a better way?
 
Back
Top