auto-fill in the gaps - advise plz

[thewiseguy]

i have a query that provides data like so:

IDNumber - Units - WeekNo
55643 - 180 - 1
55643 - (Null) - 2
55643 - (Null) - 3
55643 - 135 - 4
55643 - (Null) - 5
55643 - 0 - 6
55643 - (Null) - 7

is there a way of adding something to the SQL to make it fill in the Null values to the most previous value of Units? in other words, the top 2 Null's would become 180, the next one down would become 135 and the bottom one would become 0.

since data is not entered weekly, these Null's are inherently there for certain weeks and i am hoping that with the Nulls replaced, i can produce a bar chart from this query for a report. if the Null's remain, i am getting 'gaps' in my chart which look like zero's.

many thanks.

 

[Jon K]

I have attached a database, which contains your sample data.

You can run the query in the database. The AutoFill() function used in the query is in Module1.
.

 

[thewiseguy]

many thanks

jon k - yr a legend - works a dream