Time calculations

[Nicky]

I want to add a start time and calculated minutes together to give a finish
time for a production plan... Start time and mins are both on the report I
then want to put the planned finish time for each line on the report. I keep
getting an error.

[Start time]+[Mins]=

06:00 + 83 = 07:23

thanks

 

[John W. Vinson]

I want to add a start time and calculated minutes together to give a finish
time for a production plan... Start time and mins are both on the report I
then want to put the planned finish time for each line on the report. I keep
getting an error.

[Start time]+[Mins]=

06:00 + 83 = 07:23

thanks

A Date/Time field is actually stored as a Double Float number, a count of days
and fractions of a day (times) since midnight, December 30, 1899. As such if
you add 83 to a date/time you'll be adding 83 days (not 83 minutes!)

Try

=DateAdd("n", [Mins], [Start Time])

where Mins is an integer number of miNutes ("m" is Months) and [Start Time] is
your date/time field.

Note that this will give unexpected results if the Start Time is just a pure
time field (that is, #12/30/1899 06:00#) and the minutes carry the result over
into the next day; your start time should probably include both the date AND
the time, e.g. a StartTime of #12/29/2008 06:00#. Adding 1520 minutes to this
will give #12/30/2008 08:00#.

 

[George Hepworth]

The DateAdd() function will provide the calculations you need:

Search Access Help for information on using this, and other, date handling
functions.

 

[Nicky]

Hi John.

That worked great, but what I forgot was that I have numerous lines, I need
it to then pick up the last finish time and add on the minutes... if this
makes sense.

Start time is 06:00

mins Finish
15.76 06:15:00 this one correct
36.36 06:36:00 this should be 06:52
36.36 06:36:00 this should be 07:29
75.85 07:24:00 this should be 08:45


Thanks for your help...

I want to add a start time and calculated minutes together to give a finish
time for a production plan... Start time and mins are both on the report I
then want to put the planned finish time for each line on the report. I keep
getting an error.

[Start time]+[Mins]=

06:00 + 83 = 07:23

thanks

A Date/Time field is actually stored as a Double Float number, a count of days
and fractions of a day (times) since midnight, December 30, 1899. As such if
you add 83 to a date/time you'll be adding 83 days (not 83 minutes!)

Try

=DateAdd("n", [Mins], [Start Time])

where Mins is an integer number of miNutes ("m" is Months) and [Start Time] is
your date/time field.

Note that this will give unexpected results if the Start Time is just a pure
time field (that is, #12/30/1899 06:00#) and the minutes carry the result over
into the next day; your start time should probably include both the date AND
the time, e.g. a StartTime of #12/29/2008 06:00#. Adding 1520 minutes to this
will give #12/30/2008 08:00#.

 

[John W. Vinson]

Hi John.

That worked great, but what I forgot was that I have numerous lines, I need
it to then pick up the last finish time and add on the minutes... if this
makes sense.

Start time is 06:00

mins Finish
15.76 06:15:00 this one correct
36.36 06:36:00 this should be 06:52
36.36 06:36:00 this should be 07:29
75.85 07:24:00 this should be 08:45

Not sure I understand. It looks like you're treating the table as if it were a
spreadsheet, in which the records are in a specific order. That's NOT the case
for a table - a Table has NO ORDER, it's a "bag" full of records. If, when you
start, you just have four records with different values of [mins] in them,
then there is no way to do what you ask.

Do you have some *other* field - a timestamp, a sequential autonumber - in the
table which can be used to establish an order? What's the context? Where is
this being used?

 

[Nicky]

Hi John.

Sorry, I didn't explain myself very well. The information from the query
goes onto a report (factory production plan). I want to be able to put the
planned finish time by the side of each line. Each line does have its own
sequential number.
Hope this explains it a bit better.

Nicky

Hi John.

That worked great, but what I forgot was that I have numerous lines, I need
it to then pick up the last finish time and add on the minutes... if this
makes sense.

Start time is 06:00

mins Finish
15.76 06:15:00 this one correct
36.36 06:36:00 this should be 06:52
36.36 06:36:00 this should be 07:29
75.85 07:24:00 this should be 08:45

Not sure I understand. It looks like you're treating the table as if it were a
spreadsheet, in which the records are in a specific order. That's NOT the case
for a table - a Table has NO ORDER, it's a "bag" full of records. If, when you
start, you just have four records with different values of [mins] in them,
then there is no way to do what you ask.

Do you have some *other* field - a timestamp, a sequential autonumber - in the
table which can be used to establish an order? What's the context? Where is
this being used?

 

[John W. Vinson]

Sorry, I didn't explain myself very well. The information from the query
goes onto a report (factory production plan). I want to be able to put the
planned finish time by the side of each line. Each line does have its own
sequential number.
Hope this explains it a bit better.

Please describe the structure of your table, including the sequential number.
You'll need a query or DSum() function call of some sort to add up the times
for the previous records, but since I don't know anything about the structure
of your data I can't suggest a specific syntax.

 

[Nicky]

Hi John

I am running a query from a very large database and I have added the
relevant part of the data from the query below. The start time is being
entered when opening the report using a text box.[Start Time]

SEQ PARTNO DESCR UNITS mins Finish
10 F13583 Product 1 2800 84.85 07:24:00
20 F13581 Product 2 6800 206.06 09:26:00
30 F13585 Product 3 4200 127.27 08:07:00
40 F13586 Product 4 2400 72.73 07:12:00

Thanking you for your help so far, it is much appreciated...

 

[John W. Vinson]

Hi John

I am running a query from a very large database and I have added the
relevant part of the data from the query below. The start time is being
entered when opening the report using a text box.[Start Time]

SEQ PARTNO DESCR UNITS mins Finish
10 F13583 Product 1 2800 84.85 07:24:00
20 F13581 Product 2 6800 206.06 09:26:00
30 F13585 Product 3 4200 127.27 08:07:00
40 F13586 Product 4 2400 72.73 07:12:00

Thanking you for your help so far, it is much appreciated..

You can't "enter" anything into a Report textbox, since reports are not
editable! Assuming that [Start time] is the name of a control on an editable
Form named frmCrit, you can define Finish in the Query by typing

Finish: DateAdd("n", DSum("Mins", "[queryname]", "[SEQ] <= " & [SEQ]),
[Forms]![frmCrit]![Start time])

in a vacant field cell in the query.