Can Anyone Figure This Out?????

Hi, I have a database:-

http://uk.geocities.com/mainymainymainy/db1.mdb

I am trying to do a report on Query1 (values column). I just cant seem to get the blank fields to populate with the previous fields. can anyone help?





What i want the database to do is populate the "value" of the previous value if there is no value present. As i am tracking changes and need to produce graphs. So if there is no value there on a date then it will find the previous value and populate itself with that value.

So if on the 01/01/2005 the value is 10
And on the 02/01/2005 the value is null - how do i get the value to be 10?


Do i need a dlookup statement?

Any help would be great,
Cheers,
Mainy

It looks like makeup, please don't do it or at least do the estimation of the value with a more statistical aproach.
But still, You can browse across the database, and check the value if IsNull(regs!value) if so update the value (sorry I couldn't see your database, maybe my VB6 has not the right version of visual data manager).

how about using dmax - something along the lines of:
dmax("value","table1", {date on tbl} < {date of current record})

should return the previous value before the current records date

This is one way to do this in VB6. I hope that is what you are looking for.

There are two additional functions here which are absolutely invaluable when using DBs in VB.

Anyway I hope it helps.

'***************

Private SetDBValues()
Dim prevVal As Integer
Set dbTable = OpenDatabase("db1") 'With appropriate path
Set rsField = dbTable.OpenRecordset("SELECT * FROM Query1")
rsField.MoveFirst
prevVal = ChkIVal(rsField.Fields("Value").Value) ' Get the value of the first record
With rsField
While Not .EOF
.MoveNext
If ChkIVal(.Fields("Value").Value) = 0 Then 'If the value of 'Value' =0 then
.Edit 'edit the record
.Fields("Value").Value = prevVal 'make this 'Value' = the previous 'Value'
.Update 'and update
Else 'or
prevVal = ChkIVal(.Fields("Value").Value) 'update the previous value to this record's value
End If
Wend
End Sub


Public Function ChkIVal(DataB As Variant) As Integer
'Interesting function which returns the Integer value of the
'supplied variant.

If IsNothing(DataB) Then
ChkIVal = 0
Else
ChkIVal = CInt(DataB)
End If
End Function


Public Function IsNothing(Value As Variant) As Boolean
'Interesting function to test if there is any value to the
'passed variant.

Dim vTest As Variant
Dim iArrayStart As Integer
Dim iCtr As Integer
Dim bFlag As Boolean

If IsEmpty(Value) Then
IsNothing = True
Exit Function
End If

If IsNull(Value) Then
IsNothing = True
Exit Function
End If

If VarType(Value) = vbString Then
If Value = "" Then
IsNothing = True
Exit Function
End If
End If

If IsNumeric(Value) Then
If Value = 0 Then
IsNothing = True
Exit Function
End If
End If

If IsObject(Value) Then
If Value Is Nothing Then
IsNothing = True
Exit Function
End If

End If

If IsArray(Value) Then
On Error Resume Next
vTest = Value(0)
iArrayStart = IIf(Err.Number = 0, 0, 1)
Err.Clear
On Error GoTo 0
For iCtr = iArrayStart To UBound(Value)
If Not IsNothing(Value(iCtr)) Then
bFlag = True
Exit For
End If

Next
IsNothing = Not bFlag
End If

End Function

'*********************