By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Calculated Table Field based on Previous Record

P: 6
I understand that Access stores information as members not as related records...that being said.

I have a large set of data with a userid field, receipt check time and date. Each userid has approx. 150 receipt check times in a day. I need to calculate the individual duration time of each receipt check. In excel, I sorted the time by date, userid name, and receipt check time ascending. If the userid in row b was equal to userid in row a, I would subtract row a receipt check time from row b receipt check time to give a duration time.

I need to do this in Access but am lost.
Feb 17 '17 #1

✓ answered by gnawoncents

Nikita,

Try adapting the code below. Remember, when you open the recordset to filter for the date and userid you want (this example doesn't do that).

Expand|Select|Wrap|Line Numbers
  1. Dim dteEnd As Date
  2. dteEnd = 0
  3.  
  4. Dim dbs As Database
  5. Dim rst As Recordset
  6.  
  7. Set dbs = CurrentDb
  8. Set rst = dbs.OpenRecordset("tblReceiptChecks")
  9.  
  10. rst.MoveLast
  11. Do While Not rst.BOF
  12.     If dteEnd <> 0 Then
  13.         rst.Edit
  14.         rst!ctlElapsedTime = DateDiff("n", rst!ctlReceiptCheckTime, dteEnd)
  15.         rst.Update
  16.     End If
  17.     dteEnd = rst!ctlReceiptCheckTime
  18.   rst.MovePrevious
  19. Loop
  20.  

Share this Question
Share on Google+
3 Replies


gnawoncents
100+
P: 212
If I understand correctly, if receipt check 1 happens at 10:00 and receipt check 2 at 10:15, you need receipt check 1 to reflect a 15 min duration, right?

Someone else might have a simpler method, but you could always do it in VBA by creating a recordset (filtered by date and userid) then move through the records from last to first (Do until BOF, Previous, Loop), capturing the last time then subtracting the time from the one previous to get the elapsed time. Store that time in the current record, then move to the previous and so on until you are through all the records.

I haven't tested this, so anyone who knows, please shout out if there is a better way.
Feb 17 '17 #2

P: 6
Thanks, that idea works for me. I'm a little stuck. I have:

Dim db as Database
Dim tdf as DAO.TableDef
Dim rsin as DAO.Recordset
Dim field as DAO.Field

Set db = CurrentDB
Set rsin = dbs.OpenRecordset ("Daily Info 2")
rsin.MoveLast

Do While Not rsin.BOF
rst.MovePrevious


I'm lost on the language for storing a field value moving to the previous one and subtracting that stored value into an output for a new field on that record. These timestamps are in a field called timeaccurate.

Apologies for the cluelessness.

Nikita
Feb 17 '17 #3

gnawoncents
100+
P: 212
Nikita,

Try adapting the code below. Remember, when you open the recordset to filter for the date and userid you want (this example doesn't do that).

Expand|Select|Wrap|Line Numbers
  1. Dim dteEnd As Date
  2. dteEnd = 0
  3.  
  4. Dim dbs As Database
  5. Dim rst As Recordset
  6.  
  7. Set dbs = CurrentDb
  8. Set rst = dbs.OpenRecordset("tblReceiptChecks")
  9.  
  10. rst.MoveLast
  11. Do While Not rst.BOF
  12.     If dteEnd <> 0 Then
  13.         rst.Edit
  14.         rst!ctlElapsedTime = DateDiff("n", rst!ctlReceiptCheckTime, dteEnd)
  15.         rst.Update
  16.     End If
  17.     dteEnd = rst!ctlReceiptCheckTime
  18.   rst.MovePrevious
  19. Loop
  20.  
Feb 17 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.