I am stuck with DAO need help please
Dear All
I am a bit new to DAO and I am trying to figure out the following problem:
table (suniltest)
Reference F0F8TA Movement
1 200 +200
2 400 +200
3 100 -300
What I am trying to achieve is to get the Movement figure but I am having no
luck, I have tried the following codes with no avail, I am able to get the
result in a string with the second code but what I really need some thing
more like the first code:
My first attempt was the following:
Function Move(Field As Variant) As Variant
Dim db As Database
Dim rst As Recordset
Dim varValue1 As Variant, varValue2 As Variant, calc As Variant
Set db = CurrentDb
Set rst = db.OpenRecordset("suniltest", dbOpenDynaset)
varValue1 = Field
rst.MoveNext
varValue2 = rst!F0F8TA
calc = varValue2 - varValue1
Move = calc
End Function
Second Attempt:
Function Move() As Variant
Dim db As Database
Dim rst As Recordset
Dim varValue1 As Variant, varValue2 As Variant, calc As Variant
Dim c As Integer, result As String
Set db = CurrentDb
Set rst = db.OpenRecordset("suniltest", dbOpenDynaset)
c = 1
Do
If c = 1 Then
rst.MovePrevious
If rst.BOF Then
rst.MoveNext
calc = rst!F0F8TA
c = c + 1
End If
result = calc
ElseIf c > 1 Then
Do Until rst.EOF
varValue1 = rst!F0F8TA
rst.MoveNext
If rst.EOF Then
Move = result
Exit Function
Else
varValue2 = rst!F0F8TA
End If
calc = Str(varValue2 - varValue1)
result = result & "," & calc
Loop
End If
Loop
End Function
Could some give me the correct code as I have tryed so many different ways
but it does not work and I am now close to tears. ;-|
Kindest Regards
Sunil
