Global Variable in Query 1

[kennedymr2]

I need to determine the diffence between the current record and the previous record... for one field only.

I see to be able to achieve this using globals. see below.

In my query i do get exactly the result i need., but when i test the field in the query eg 200.. it does not recognise it as a numeric or alpha field..

Appreciate any ideas...
------------------------------------------

Option Compare Database

Global GPH As String
Global GPrevious As Long


Public Function GDifference(GH As String, GD As Long) As Long

If GH <> GPH Then GPrevious = 0

GDifference = 0
If GPrevious > 0 Then GDifference = GD - GPrevious

GPrevious = GD
GPH = GH

End Function

 

[MajP]

I would do it in a query instead of a global. Then if you need to you can pass the field values in as parameters.
tblOne:
[tt]
autoID GH GD
1 a 10
2 b 15
3 c 20
4 d 5
5 e 0
6 f 45
[/tt]

query

SELECT 
 A.GH, 
 A.GD, 
 (select top 1 tblOne.GD from tblOne where A.autoID > 
 tblOne.AutoID order by tblOne.autoID DESC) AS GDPrevious,  
 (select top 1 tblOne.GH from tblOne where A.autoID > tblOne.AutoID order by tblOne.autoID DESC) AS GHPrevious
 FROM tblOne AS A
 ORDER BY A.autoID;

This results in

[tt]
GH GD GDPrevious GHPrevious
a 10
b 15 10 a
c 20 15 b
d 5 20 c
e 0 5 d
f 45 0 e
[/tt]

 

[kennedymr2]

Majp...

Thanks for the idea...I agree this is probably the best way to do it

regards Kennedymr2