Solved how to subtract between last two rows using VBA

[Pop_Access]

in the main screen "Master", it's contains a subform "Visits Subform", inside the sub form i have fields "Visit_date" and "Countd".
when i click on add button for new record in the subform, i want to subtract the last record in "Visit_date" field and the current record that i want to add now, and the result to be saved in field "Countd".

knowing that the table name is visits_list.

thanks in advance

 

[CJ_London]

there are a number of things to be clarified - perhaps obvious to you, but potentially can cause problems.

Define 'last record'. databases have no inherent sense of order. data is stored randomly. So to define last, you need to also define an order. You may also need to define a subset of data. So do you mean last based on ordering on the last record entered? or last based on the latest date prior to today? or prior to when you are entering the data?

And since these are visits - is that any visit by anyone? or a visit by the same person? And what do you want to happen for a first visit when there will not be a previous record.

Finally, you should not save this sort of data (one of the big differences between excel and access), just calculate it when required. That way, if a date is changed for some reason, you don not have a whole lot of other records to update.

 

[MajP]

ID	vDate
1	1/10/2020
2	1/15/2020
3	1/23/2020
4	1/30/2020

This can be calcualted in a query.

SELECT 
  B.vDate, 
 (Select Top 1 A.Vdate from tblDates as A where A.Vdate <B.Vdate order by A.ID DESC) AS PreviousDate, 
 [Vdate]-[PreviousDate] AS CountD
FROM 
 tblDates AS B;

output

vDate	PreviousDate	CountD
1/10/2020		
1/15/2020	1/10/2020	5
1/23/2020	1/15/2020	8
1/30/2020	1/23/2020	7

 

[Pop_Access]

there are a number of things to be clarified - perhaps obvious to you, but potentially can cause problems.

Define 'last record'. databases have no inherent sense of order. data is stored randomly. So to define last, you need to also define an order. You may also need to define a subset of data. So do you mean last based on ordering on the last record entered? or last based on the latest date prior to today? or prior to when you are entering the data?

And since these are visits - is that any visit by anyone? or a visit by the same person? And what do you want to happen for a first visit when there will not be a previous record.

Finally, you should not save this sort of data (one of the big differences between excel and access), just calculate it when required. That way, if a date is changed for some reason, you don not have a whole lot of other records to update.

i have database to manage the visits of my teams to the customers, so i need to calculate the duration days between the last visit and the current visit, (I have solution for the first visit in case we do not have previous visit date) but when the team visited my customer and they want to document it on the database when press on (add new visit) i need to know the duration between the previous visit the the current visit date to be saved on field named (countd) on the same table.

hope i explained to you my needs.

thank you

 

[Pop_Access]

ID	vDate
1	1/10/2020
2	1/15/2020
3	1/23/2020
4	1/30/2020

This can be calcualted in a query.

SELECT 
  B.vDate, 
 (Select Top 1 A.Vdate from tblDates as A where A.Vdate <B.Vdate order by A.ID DESC) AS PreviousDate, 
 [Vdate]-[PreviousDate] AS CountD
FROM 
 tblDates AS B;

output

vDate	PreviousDate	CountD
1/10/2020		
1/15/2020	1/10/2020	5
1/23/2020	1/15/2020	8
1/30/2020	1/23/2020	7

thank you for your efforts;

can i used VBA instead of query

 

[CJ_London]

can i used VBA instead of query

you can use the dmax function to find the last visit date

dmax("visit_date","visits_list","CustomerPK=" & me.txtcustomerPK & " and visit_date<#" & format(me.txtvisitdate,"mm/dd/yyyy") & "#")

you have not provided any info regards the current record or when you want the code to run so you will need to subtract the value returned by dmax from whatever date applies to the current record - use the datediff function

 

[MajP]

can i used VBA instead of query

You can show this dynamically via vba in many ways, but we are all suggesting not to store the value.

The issue with storing calculated value is that any change in the data after the initial input has to be accounted for via code. It is very easy to get your calculated values out of synch. Therefore if it can be done dynamically do it dynamically.

There are some extremely complex calculations that cannot be done dynamically, so they may have to be stored for sake of speed. You then have to be very diligent to recalcuate at specific times.