Using DLookup to find a value in a field.

[julianholworth]

I am trying to create a calculated field. One of the components of that formula, is taking the value from the field before. Is there anyway to get that value using DLookup? As far as i can know, Dlookup only finds a value when you specify a criteria.

[orange]

I suggest you step back and tell us in plain , simple English WHAT you are trying to accomplish. Once readers ubderstand your issue in context, you'll get some focused responses and options.

[julianholworth]

Like in Excel where I can point to a prior cell as a piece of a function ( =D3*2/(D2+1) ), I wan to be able to do the same

This functionality within the Access's Expression builder eludes me. This is for Exponential moving averages, which need the previous Exponential value to complete the formula for the current value.

[orange]

I am not an Excel person. Perhaps you could tell and show us with an example WHAT you are trying to accomplish.

I did find this

Exponential Moving Average Calculation

Exponential moving averages reduce the lag by applying more weight to recent prices. The weighting applied to the most recent price depends on the number of periods in the moving average. There are three steps to calculating an exponential moving average. First, calculate the simple moving average. An exponential moving average (EMA) has to start somewhere so a simple moving average is used as the previous period's EMA in the first calculation. Second, calculate the weighting multiplier. Third, calculate the exponential moving average. The formula below is for a 10-day EMA.

SMA: 10 period sum / 10

Multiplier: (2 / (Time periods + 1) ) = (2 / (10 + 1) ) = 0.1818 (18.18%)

EMA: {Close - EMA(previous day)} x multiplier + EMA(previous day).


A 10-period exponential moving average applies an 18.18% weighting to the most recent price. A 10-period EMA can also be called an 18.18% EMA. A 20-period EMA applies a 9.52% weighing to the most recent price (2/(20+1) = .0952). Notice that the weighting for the shorter time period is more than the weighting for the longer time period. In fact, the weighting drops by half every time the moving average period doubles.

[julianholworth]

I already have a formula for EMA's and runs perfectly on Excel. I was trying to do the same in Access.

As far as i can understand, DLookup like Query uses criteria to find a value. I am trying to point to a certain field within my table and use that value to calculate the EMA's

This is a sample of what i have done in Excel.

EMA = B5*2/(100+1)+C4*(1-2/(100+1)), where B5 and C4 refer to a specific field where its value can be anything.

Date	Close	EMA
5/11/2016	21.44	23.46
5/12/2016	22.36	23.44

[ssanfu]

Have you seen this thread from 04-07-2016? See Post #8
https://www.accessforums.net/showthread.php?t=59165

[orange]

Good eyes Steve -- I don't remember that thread. I started looking in some of my sample databases for moving average.
Geez it's only a month ago....gettin' old.

I did some more searching and found this link re EMA.
I have not found an example using Access and vba.