Reading Previous record of field and inserting it into current If Is Null??

[likeyeah]

I have been working on a glidepath (activities remaining in a project) and after some assistance from Bob B. on
here have developed the queries to give me the activities planned, and completed over the financial weeks.

However, I have to combine the two tables using a union query to get all possible weeks that exist in both, as
some weeks have no planned completions (because it is a phased project, i.e. you need to close so many activies by
Date X) and some weeks no activities were closed.

Using a Union query therefore produces a list of week numbers but there are gaps where for example as below in
Query 1, there are none planned and the field is null.

If it was possible to insert the previous records value into this field, simplistically "If Is Null
(CurrentRecord=PreviousRecord)" then this would fill in the gaps and give a query result as in Query 2

THis is only required because as the chart object in 2003 is a cut down version, it does not interpolate between
data points, so you get a few points plotted, and then large gaps, so when the chart is trying to compare actuals
to plannedi it's not helpful...

Any advice about EOF#, Current# etc. gratefully received.!

J


Query 1


Week NumJobsPlanned
0802 812
0806 809
0915 783
0917 777
0921
1002
1004
1005 776
1009 774
1010
1011
1012
1013 672
1014
1015

Query 2

Week NumJobsPlanned
0802 812
0806 809
0915 783
0917 777
0921 777
1002 777
1004 777
1005 776
1009 774
1010 774
1011 774
1012 774
1013 672
1014 672
1015 672

 

[likeyeah]

Been having a play with a recordset method of doing this, practicing with a test table. The code below picks up
when it is null or not, but getting errors trying to put in the previous value into the current record, then
moving on again.

Also will need error trapping in case the first record is null..

J







Private Sub amendrecords_Click()
On Error GoTo Err_amendrecords_Click

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim prevnum As Long



strSQL = "select * from [tbltest]"

Set dbs = CurrentDb()

' Open the recordset
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

'move through the recordset to get an accurate count
rst.MoveLast
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
prevnum = rst!Planned
'rst.Edit
'rst!FieldName = <New Value>
'rst.Update
rst.MoveNext
If IsNull(rst!Planned) Then
MsgBox "It's a Null"
Else
MsgBox "It's not a Null"
End If

Loop
End If

'cleanup
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Exit_amendrecords_Click:
Exit Sub

Err_amendrecords_Click:
MsgBox err.Description
Resume Exit_amendrecords_Click

End Sub

 

[Bob Barrows]

Perhaps:
UPDATE tbltest as o
SET Planned =
(
Select Planned FROM tbltest
WHERE keyfield=
(
SELECT max(keyfield) FROM tbltest
WHERE Planned is not null and keyfield<o.keyfield
)
)
WHERE Planned Is Null

Been having a play with a recordset method of doing this, practicing
with a test table. The code below picks up when it is null or not,
but getting errors trying to put in the previous value into the
current record, then moving on again.

Also will need error trapping in case the first record is null..

J

<snip>