calculate difference in rows on a form

[Glenna]

I need to calculate the difference between months by row on a form. I am
displaying the information below based on numbers entered by the user. I
can't get the column Monthly Claim to show on the form or in a query. I can
do it in Excel but not access. I need to calculate the difference from the
February to January JTD Rev Clm column and populate the data in the Monthly
Claim column so the user knows what they can claim for the month. I received
some help on a query for the same question but was unable to execute the
suggestion given. I posed this question there but thought this might
generate a different response since it's in regards to a form not a query.

Order Month JTD Rev Clm Monthly Claim
101034011 January 47,523.90 47,523.90
101034011 February 48,407.25 883.34
101034011 March 49,607.85 1,200.60

 

[KARL DEWEY]

Your table needs a DateTime field instead of text month to handle this.
SELECT Order, Format([ClaimDate], "mmmm") AS Month, [JTD Rev Clm], (SELECT
[XX].[JTD Rev Clm] - YourTable.[JTD Rev Clm] FROM YourTable AS [XX] WHERE
Format([XX].[ClaimDate], "yyyymm") = Format(DateAdd("m", 1,
[YourTable].[ClaimDate]), "yyyymm") ORDER BY [XX].[ClaimDate]) AS [Monthly
Claim]
FROM YourTable
ORDER BY [ClaimDate];