Find elapsed time

[aman1234]

Hi guys

I have a form that populates the values in the table: Reference,Name,Starttime,Finishtime,elapsedtime

My question is the tables stores the data as follows:
Reference Name Start Finish Elapsedtime Volume

10001 Derek 09:00 09:10 100
10001 Derek 09:15 09:30 0:05 20
10001 Bob 09:35 09:40
10001 Bob 09:47 09:55 0:07
I want to find out the elapsed time(Diff between Finish time of first record and Starttime of next record) e.g the text in bold 0:05. and it should be for the same person. e.g here Derek name is same for the first two records thats why the elapsed time is 0:05.

I hope anyone can help me out.

Thanks
Aman

[DonkeyOte]

A sample file would invariably help but if we assume first name appears in B2 with elapsed time being column E then:

E2: =IF(OR(B3=B2,B2<>B1),"",MOD(C2-D1,1))
copied down
format as say [mm]:ss

Note above assumes a max of 2 rows for any given name and that they appear sequentially.

[aman1234]

Hi Thanks for your help. But the data is not saved in excelsheet,It gets saves in access table. In my project the frontend is Excel and backend is access.

Thanks
Aman

[DonkeyOte]

You might need to outline your question again as I'm afraid I don't follow.

Either the question pertains to:

a) Excel - ie populate table via Formula / code and write results to Access

b) Access - ie update Access table post update from Excel (via SQL)

If the latter then have the question moved to the Access forum.

If the former, outline your requirements more fully.

[aman1234]

I have designed forms in excel but they populate tables in access. so when the user press Enter button on the userform then it will put the values of ref,name,starttime,finishtime,volume textboxes into access table. I have written the following code but I jhave no idea how to calculate elapsed time.

Please Login or Register  to view this content.

[DonkeyOte]

Still confused I'm afraid...

...I want to find out the elapsed time (Diff between Finish time of first record and Starttime of next record)

Given your form contains only one record I'm not quite sure how you're hoping to do this "client-side" given the duration is dependent upon prior record info.
I suspect you would be better off determining this via SQL once all records are loaded - perhaps at the point of running retrieval queries
(else the order in which records are added becomes of paramount importance regards data integrity)

[aman1234]

Still confused I'm afraid...



Given your form contains only one record I'm not quite sure how you're hoping to do this "client-side" given the duration is dependent upon prior record info.
I suspect you would be better off determining this via SQL once all records are loaded - perhaps at the point of running retrieval queries
(else the order in which records are added becomes of paramount importance regards data integrity)

I have written the following code but it gives runtime error 2905 reserved error message at the following DMax statement.

Please Login or Register  to view this content.

and call the function through the following statement:

Please Login or Register  to view this content.

Please can you figure out my problem.

Thanks in advance 4 your help.
Aman

[aman1234]

Can anyone please solve my problem?

Thanks in advance for any help.

Aman

[aman1234]

Hi Folks

Can anyone please have a look at my problem? Its very urgent to resolve so please help me out.

Thanks
Aman