How to check if field value is bigger or smaller than previous field value.

[SGT]

Good day all

I have a form to which I enter data like Height, Weight, Waist etc. Once a week a member is weighed and measured and that information is captured in the form. So there can be many entries for just one person. What I want to do is every time the new data is entered, for instance the weight, it must check the previous weight of that member and indicate if that member lost (Image1), gained (Image2) weight or stayed the same (Image3) by enabling one of three images. To identify a member I use a [paynumber] field. It must just use the last record for the [paynumber] field on the form. The form is called frm_ef_memberfitness. The primary key is [memberfitnessID].

I hope this helps and makes sense.

[ranman256]

(the long way) using queries. On a form, enter the [paynumber] to get the person. Using this run these queries...

Make Q1 that gets the TOP 1 record sorting [date] descending

Make Q2 that gets the TOP 1 record sorting [date] descending < Max([date]) ''this gets the 2nd highest date BUT make all the fields negative.
(select -[weight], -[height]....from table)

Q3: You could use a UNION query of the 2,
select * from Q1
union
select * from Q2

Q4: sum Q3