Grabbing Data from Previous Row

[SpyhunterUK]

I have a form with sub form. The sub form shows data in Date Order and as entries are added I have a button that sorts this in date order as well.

I need to be able to grab the previous row (after sorting) to calculate a currency so example:

Row 1 - 2/1/24 - £2
Row 2 - 1/1/24 - £1

After Date Order sorted:
Row 1 - 1/1/24 - £1
Row 2 - 2/1/24 - £2

I need to be able to add the rows as they go so like this: (after date and row sort)
Row 1 - 1/1/24 - £1 £1
Row 2 - 2/1/24 - £2 £3

Whereas if not sorted it looks like this:
Row 1 - 2/1/24 - £2 £2
Row 2 - 1/1/24 - £1 £3

So the main point being they need to be in date order and the previous row field grabbed. Can anyone help?

I have a row sort order in the query but when I add or refresh/requery the sub form it keeps this row order and doesn't produce a new one?

Help!

 

[DHookom]

Are there just two rows or could there be more or less? Are you trying to create a running balance? Can we assume the dates have gaps?

 

[theDBguy]

Not sure if this is what you want but try doing a search on "Running Sum."

 

[SpyhunterUK]

yes, i have the running sum working if on record ID - but I want to add new entries and resort by date and this does not work as I want it to, as some of the ID's may have dates out of order, if that makes sense.

 

[theDBguy]

yes, i have the running sum working if on record ID - but I want to add new entries and resort by date and this does not work as I want it to, as some of the ID's may have dates out of order, if that makes sense.

Show us your SQL statement. Have you tried using the date column instead of the ID for your running sum?

 

[SpyhunterUK]

yes but the date column doesnt work when i try it, as that would fix it lol

SELECT TBL_Money.ID, TBL_Money.Date, TBL_Money.Day, TBL_Money.Month, TBL_Money.InOut, TBL_Money.InOut, TBL_Money.Amount, TBL_Money.Payee, DSum("[Amount]","TBL_Money","[ID]<=" & [ID]) AS [Running Total]
FROM TBL_Money
ORDER BY TBL_Money.Date;

 

[arnelgp]

can you try:

SELECT TBL_Money.ID, TBL_Money.Date, TBL_Money.Day, TBL_Money.Month, TBL_Money.InOut, TBL_Money.InOut, TBL_Money.Amount, TBL_Money.Payee, DSum("[Amount]","TBL_Money","[Date]<=#" & Format([Date], "mm/dd/yyyy") & "#") AS [Running Total]
FROM TBL_Money
ORDER BY TBL_Money.Date;

 

[Gasman]

Everything with the same date is going to have the same value though?

 

[LarryE]

I have a form with sub form. The sub form shows data in Date Order and as entries are added I have a button that sorts this in date order as well.

I need to be able to grab the previous row (after sorting) to calculate a currency so example:

Row 1 - 2/1/24 - £2
Row 2 - 1/1/24 - £1

After Date Order sorted:
Row 1 - 1/1/24 - £1
Row 2 - 2/1/24 - £2

I need to be able to add the rows as they go so like this: (after date and row sort)
Row 1 - 1/1/24 - £1 £1
Row 2 - 2/1/24 - £2 £3

Whereas if not sorted it looks like this:
Row 1 - 2/1/24 - £2 £2
Row 2 - 1/1/24 - £1 £3

So the main point being they need to be in date order and the previous row field grabbed. Can anyone help?

I have a row sort order in the query but when I add or refresh/requery the sub form it keeps this row order and doesn't produce a new one?

Help!

Try sorting in your query first by date and second by ID. So if dates are the same, they will sort then by ID.

 

[The_Doc_Man]

If you find that you do something to the table and suddenly some records are out of order on the form, you might need a .Requery to re-establish the order. But you said that didn't appear to work. Which leads to the question, are you saying explicitly that after the .Requery you still get rows out of order even though they have different dates? Forms can be sorted either of two ways. You can have a form's .RecordSource that contains an ORDER BY clause, or there is a form property .OrderBy (with its necessary partner, .OrderByOn). Which of these are you using?

Another thing I noticed is that the field name is "Date" which is probably a no-no, compounded by not always being in brackets. It is unlikely but possible that your query is confusing the field name with the function name for Date() and thus sorting in no apparent order. Even though your SQL shows appropriate context to qualify the references, stranger things have been known to happen. If you can change the field name to not match the function name, that might help.

 

[theDBguy]

yes but the date column doesnt work when i try it, as that would fix it lol

SELECT TBL_Money.ID, TBL_Money.Date, TBL_Money.Day, TBL_Money.Month, TBL_Money.InOut, TBL_Money.InOut, TBL_Money.Amount, TBL_Money.Payee, DSum("[Amount]","TBL_Money","[ID]<=" & [ID]) AS [Running Total]
FROM TBL_Money
ORDER BY TBL_Money.Date;

If Arnel's suggestion doesn't work, can you post a sample db?

 

[SpyhunterUK]

can you try:

SELECT TBL_Money.ID, TBL_Money.Date, TBL_Money.Day, TBL_Money.Month, TBL_Money.InOut, TBL_Money.InOut, TBL_Money.Amount, TBL_Money.Payee, DSum("[Amount]","TBL_Money","[Date]<=#" & Format([Date], "mm/dd/yyyy") & "#") AS [Running Total]
FROM TBL_Money
ORDER BY TBL_Money.Date;

WoW!! Thanks. I think that worked. I will do some testing to be sure, but adding a new line with a date in between listed dates, then refreshing sub form gives me the correct running total

 

[SpyhunterUK]

Seems to work Super Thanks.
One other thing, how do I make it so the running sum can be currency, as I cannot fix this as its not a real field.

 

[The_Doc_Man]

There is always the possibility of using a "Currency" format. As long as your running sum field doesn't need more than 12 or 13 digits (including two decimal places), that should work.

 

[Gasman]

WoW!! Thanks. I think that worked. I will do some testing to be sure, but adding a new line with a date in between listed dates, then refreshing sub form gives me the correct running total

What happens when you have two dates the same? Can that happen?

 

[DHookom]

Typically you would just format your text box to currency.

 

[Gasman]

Typically you would just format your text box to currency.

That is how I would also do it, but recently I found a need to just have it in a query and set that field's properties.

 

[SpyhunterUK]

Ah, its not working if the same date is used (which will be the case sometimes)

 

[Gasman]

You *might* have to create our own row counter to get the differing amounts?, after the data is sorted by date, then ID.

 

[SpyhunterUK]

You *might* have to create our own row counter to get the differing amounts?, after the data is sorted by date, then ID.

I tried that but it didnt work - i will update the basic db, appreciate any help really do appreciate it all.