Hi All

I have a question. I am trying to find either a query or VBA code for Microsoft Access where i can perform a simple task which is as:

1. If the value is null, use the last available value (i.e.) 4 in our case and grow it by certain percentage like 2.5% et. So null will be replaced by 4*1.025=5 and so on until the last record.

Can you please help?

I tried SQL as well as VBA but i am bit lost.

CodePudding user response：

Open the table as a recordset using VBA and loop the records while storing the value of Value.

When the value of Value reaches a Null, update the record's Value using your formula and the stored value from the previous record, store that value and continue with the next record, update this, and so on until no more records.

CodePudding user response：

We group each value with the nulls after it. Then we fill in the nulls with that value and multiply it by 1.025 in the power of row_number() over()-1.

select  Date
       ,max(Value) over(partition by grp) * power(1.025, row_number() over(partition by grp order by Date)-1) as value
from    
(
select  * 
       ,count(Value) over(order by Date) as grp
from    t
) t

Fiddle