Obtain previous value from query

[foshizzle]

I have the following query which will run on any given date. How could I obtain the previous value for ActualBal?
i.e., if the report date is run for 5/1/21, would could I get the ActualBal for 4/30/21?

SELECT qryRMonthlySumTrans2.TransactionID, qryRMonthlySumTrans2.TransactionDate, qryRMonthlySumTrans2.AirlineCompanyLookup, qryRMonthlySumTrans2.BegBalance, qryRMonthlySumTrans2.GallonsRcvd, qryRMonthlySumTrans2.GallonsIssd, qryRMonthlySumTrans2.Total, qryRMonthlySumTrans4GainLoss.AirGainLossNz, Nz(([Total]+[AirGainLossNZ]),0) AS ActualBal FROM qryRMonthlySumTrans2 LEFT JOIN qryRMonthlySumTrans4GainLoss ON qryRMonthlySumTrans2.AirlineCompanyLookup = qryRMonthlySumTrans4GainLoss.AirlineCompanyLookup WHERE (((qryRMonthlySumTrans2.TransactionDate) Between [TempVars]![tmpStartDate] And [TempVars]![tmpEndDate])) GROUP BY qryRMonthlySumTrans2.TransactionID, qryRMonthlySumTrans2.TransactionDate, qryRMonthlySumTrans2.AirlineCompanyLookup, qryRMonthlySumTrans2.BegBalance, qryRMonthlySumTrans2.GallonsRcvd, qryRMonthlySumTrans2.GallonsIssd, qryRMonthlySumTrans2.Total, qryRMonthlySumTrans4GainLoss.AirGainLossNz;

 

[Ranman256]

on a form , put a textbox txtRunDate. User enters a date.

the query would then sum data to that date.
select sum(amt) from table where [EventDate]<=forms!fMyForm!txtRunDate

 

[foshizzle]

Sorry; to clarify, I need to be able to use the value for the previous ActualBal in a new query

 

[CJ_London]

Use the same query but change the criteria to tempvars start date-1 and same for enddate

if you want to compare, join the two queries - you’ll probably need to left join to the new query in case there isn’t a previous value

I suspect you would do better to start again with your sumtran2 query or even earlier for a more efficient query