CALCULATING TIME FROM ONE FILED

[Shafique]

Hi Everyone
I have a Subform that linked with a form's ID (Primery Key) and stored the data with forms BATCH_No (Unique ID)
"as shown in attachment
Here is want to SUM the Subform's RTIME Field's total duration

 

[Shafique]

I forget to upload the pic

 

[jackd]

Still no pic.

 

[Shafique]

Thanks for your attention Sir.
Iwant share something with maybe it solve the issue
I HAVE TWO TABLES
1: BATCH
2:B_RECIPE
I put this function in a unbound TEXTBOX IN form (BATCH)
=Dsum("RTIME","B_RECIPE","RID='"&[FORMS]![BATCH]![BATCH_NO]&"'")

THE B_RECIPE IS A SUBFORM AND IT'S [RID] IS LINKED WITH "BATCH" FORM's ID.
IN [RID] DATA WE STORE [BATCH_NO] ALSO.


SO ONE BATCH [ID] HAS MANY [RID] RECORDS IN [RTIME].
I WANT TO CALCULATE ALL RTIME FIELD THAT ASSOCIATES WITH BATCH_NO.

 

[Shafique]

in This Pic on subform a field named
RTIME with data
11:32 am
11:34 am
2:30 pm
and lots of more will be come with Time
i want to know the duration from First Time record (11:32 am) To last Time record (2:30 pm)
e.g.. 02:58

 

[Shafique]

I Tried Lot's of variants e.g..
=DSum("[RTIME]","B_RECIPE","[RID]='" & DLookUp("[BATCH_No]","BATCH","[BATCH_No]='" & [Forms]![BATCH]![BATCH_No] & "'") & "'")
but when is do like this =DSum("[RTIME]","B_RECIPE") then it calculate without critera (BATCH_No)

 

[Micron]

You cannot determine a time span by summing. 11:23 is 23 minutes after 11 A.M. If you add a similar time component you will simply create a new time on the clock. So 10:23 + 01:05 will be 11:28 AM, not some total. To determine a time span use DateDiff function but you will need to include the day date.

 

[Shafique]

You cannot determine a time span by summing. 11:23 is 23 minutes after 11 A.M. If you add a similar time component you will simply create a new time on the clock. So 10:23 + 01:05 will be 11:28 AM, not some total. To determine a time span use DateDiff function but you will need to include the day date.

I am very thankful for your valuable suggestion.
I will try. It will be more easy for me if you put some sample. how to way out.

 

[Micron]

It will be more easy for me if you put some sample.

I'll bet it would be easier for me to hand you the proverbial fish instead of teaching you how to fish. However, you have to put some effort into it. Google ms access datediff function and see what it does. As I indicated, you will likely need date values along with your times. I pointed that out, but I am none the wiser about your data after your response.

This will result in a calculation of 23 hours.
datediff("h",#01/21/2021 11:30:00 AM#,#01/22/2021 10:30:00#)

However, if all of your dates/times are in one field, you will probably have to create a calculated query field in a Totals query. Totals query will allow you to group by one or more other fields if you need to do that. Also, you might have to create 3 calculated fields like
Expr1: DMax(of field, with criteria perhaps)
Expr2: DMin(of field, with criteria perhaps)
Expr3: [Expr1] - [Expr2])

Another possibility is that you would need to use a subquery. I am just throwing ideas out here as I have no idea what your data looks like. Perhaps you will have to research DMax and DMin functions and/or subqueries.