defaulting to prior year data....Dlookup?

[Cansley]

Hi
I am building a forecasting database that will have known amounts entered for various projects. If a project has no known future amount (lets say for next March), I want my query to calculate a projected number by taking the most recent equivilent month (for this last March,which may be a number or even zero) and applying a growth factor.

I thought to use Dlookup in this way, but it is not working:

Amt: IIf(IsNull([Amount]),DLookUp([amount],"data_table",DateAdd("yyyy",-1,[data_table]![date])=[data_table]![date])*[growth],[amount])

I have created "placeholders" for the non-existant months in a prior query (i.e. this formula should replace null cells w/ projected values only if there is no actual value)

I would appreciate any suggestions very much!
thanks

 

[gromit]

Hi -

For DLookup to work with a date, you need to enclose the data with # signs.

E.g.
DLookUp("[Data]","data_table","[Date] = #12/1/2005#")

If you use a calculated date expression, it looks like this...
DLookUp("[Data]","data_table","[Date] = #" & [Expr1] & "#")

Also, you may have some syntax issues with your Amt expression. I suggest building it up piecewise in a query and check at each stage.

- g