422,764 Members | 1,289 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,764 IT Pros & Developers. It's quick & easy.

Add time in access using 2 fields

P: 43
I have a field called [StartTimeOfProduction] (manually enter)
I have a calculated field that gives me the actual production time. I need to take [StartTimeOfProduction](6:30 am Format) and say add [Runtime](in minutes say 29.78) and get a [EndTimeOfProduction]

I have been searching the net and can't seem to find an answer for this.

Thanks for the help,

DJRhino
Mar 6 '18 #1

✓ answered by twinnyfo

Simple:

Expand|Select|Wrap|Line Numbers
  1. EndTimeOfProduction: Format(DateAdd("n",[ProductionTime],[StartTimeOfProduction]),"Medium Time")

Share this Question
Share on Google+
28 Replies


twinnyfo
Expert Mod 100+
P: 2,317
Hey DJ,

First, you have an intriguing problem. Usually, we know the start and stop dates/times and have to calculate the RunTime. Yours is going in the opposite direction. However, the solution will be similar.

If your RunTime is always in a number of minutes, you should be able to use the DateAdd() function to resolve this. Your arguments would look something like this:

Expand|Select|Wrap|Line Numbers
  1. [EndTimeOfProduction] = DateAdd("n", [Runtime], [StartTimeOfProduction])
That should get you into the ballpark. As long as you are working with actual Date fields, this should work.

Hope that hepps!
Mar 6 '18 #2

P: 43
I put this as my Code:
Expand|Select|Wrap|Line Numbers
  1. EndTimeOfProduction: DateAdd("n",[ProductionTime],[StartTimeOfProduction])
Now I get #Name?
Mar 6 '18 #3

twinnyfo
Expert Mod 100+
P: 2,317
Where are you putting this code? Is this on a Form? Are you running script from a VBA module behind a Form? Something else?
Mar 6 '18 #4

P: 43
I added this to a query that is feeding my form.
Mar 6 '18 #5

twinnyfo
Expert Mod 100+
P: 2,317
Very strange....

I just validated using this in a sample query and it ran fine.

What data types are you using for [ProductionTime] and [StartTimeOfProduction]? And these are fields in your Table?
Mar 6 '18 #6

P: 43
[ProductionTime] is a calculated field from info entered into the table. [EndtimeOfproduction] is in the table currently entered manually. in medium time format
Mar 6 '18 #7

P: 43
This is how [ProductionTime] is in the query:
Expand|Select|Wrap|Line Numbers
  1. ProductionTime: ([GoodMeters]/[CalcLineSpeed]+(Nz([Scrap])/[CalcLineSpeed]))
[GoodMeters] & CalcLineSpeed] are also calculated fields.
Mar 6 '18 #8

twinnyfo
Expert Mod 100+
P: 2,317
How is ProductionTime calculated? Is it taking two separate values of minutes and adding them or it is taking two Date/Time values and returning another Date/Time?

Either way, I've tried multiple permutations and haven't been able to generate either an error or #Name? result.
Mar 6 '18 #9

twinnyfo
Expert Mod 100+
P: 2,317
You read my mind..... What are some of the values that ProductionTime gives you? This is troublesome that you are experiencing this error, when I am unable to duplicate such an error.

We may be missing something obvious.
Mar 6 '18 #10

P: 43
GoodMeters = [PartLength]*[QtyParts]/1000
CalcLineSpeed = [LineSpeed]*0.3048
ProductionTime: ([GoodMeters]/[CalcLineSpeed]+(Nz([Scrap])/[CalcLineSpeed])) - Which gives me a time in minutes in decimal form (25.45)
I want to take those minutes and added them to the [startTimeOfProduction] which is in medium time format (6:30 am)

Hope this clears it up a little.
Mar 6 '18 #11

twinnyfo
Expert Mod 100+
P: 2,317
You say
I want to take those minutes and added them to the [startTimeOfProduction] which is in medium time format (6:30 am)
But, is [startTimeOfProduction] a "Date" data type?

However, even when I use a text field of "6:30 am" in my sample query and add 21 minutes, I still get "6:51 am"!

There must be something that we are missing in your query that is preventing this from working. I am trying to break my query and it refuses to give me an error. You are trying to make yours work and it refuses to give the results you want.

(Goofy scratching his head.....)
Mar 6 '18 #12

P: 43
StartTimeOfProduction = Date/Time in the table I'm pulling from
Mar 6 '18 #13

twinnyfo
Expert Mod 100+
P: 2,317
Bad news: I've recalculated everything just like you have described and my Query still works:



Expand|Select|Wrap|Line Numbers
  1. SELECT tblDJRhino.PartLength, tblDJRhino.QtyParts, tblDJRhino.LineSpeed, tblDJRhino.Scrap, tblDJRhino.StartTimeOfProduction, [PartLength]*[QtyParts]/1000 AS GoodMeters, [LineSpeed]*0.3048 AS CalcLineSpeed, ([GoodMeters]/[CalcLineSpeed]+(Nz([Scrap])/[CalcLineSpeed])) AS ProductionTime, DateAdd("n",[ProductionTime],[StartTimeOfProduction]) AS EndTimeOfProduction
  2. FROM tblDJRhino;
Not sure what the issue is here..... Again, we gotta be missing something obvious.
Attached Images
File Type: jpg DJRhino.jpg (13.4 KB, 275 views)
Mar 6 '18 #14

P: 43
The query does have two tables on it that are related many to 1, but the fields I'm using are on just the one table. This query is linked with a form(subform) to another main form. Kind of a complicated little database.
Mar 6 '18 #15

twinnyfo
Expert Mod 100+
P: 2,317
Have you tried stripping the query of all extraneous tables, forms and what-nots? Then try recreating what I demonstrated above. See if it is something related to the complexity first. If it works, then we redirect our efforts toward decomplexifying things......
Mar 6 '18 #16

P: 43
Here is a screen shot of my query.
Attached Images
File Type: jpg Query Screenshot.jpg (21.5 KB, 23 views)
Mar 6 '18 #17

twinnyfo
Expert Mod 100+
P: 2,317
Impossible to see it that small. I can only presume it is similar to the one I provided. Can you post a list of returned values from your query? I'll se if that has anything to do with it.
Mar 6 '18 #18

P: 43
Ok I just made a query based off of the query I sent as a screenshot and the formula works perfectly, except I don't need it in seconds, but that should be just changing the format.
Mar 6 '18 #19

twinnyfo
Expert Mod 100+
P: 2,317
So....... I guess it worked? I must be sharing some of my gremlins over the internet!
Mar 6 '18 #20

P: 43
Yeah its weird that I just needed to make a query of a query to get it to work. So now if I switch my forms record source to this new query I'm hoping everything will still work.
Mar 6 '18 #21

P: 43
ok, son now I get a #error on this field if some of the data is missing, I'm assuming I need a Nz() the there somewhere. Would I put this in the front of the formula?
Mar 6 '18 #22

twinnyfo
Expert Mod 100+
P: 2,317
It could also be a divide by zero error. Math functions do not like to calculate null values, so an Nz([Value], 0) should alleviate those problems, outside of div/0 errors.
Mar 6 '18 #23

P: 43
Expand|Select|Wrap|Line Numbers
  1. EndTimeOfProduction: DateAdd("n",[ProductionTime],[StartTimeOfProduction])
Where exactly would I put Nz(Value], o
Mar 7 '18 #24

P: 43
Sorry I figured it out after I scratched my head and tried a few things. The only thing is I cannot get the seconds to disappear. I have the format set for medium time and its showing, for example 00:00:00 am
Mar 7 '18 #25

P: 43
I also noticed that when it goes past midnight that it adds a date in front of it, is there away to make this not show?
Mar 7 '18 #26

twinnyfo
Expert Mod 100+
P: 2,317
Simple:

Expand|Select|Wrap|Line Numbers
  1. EndTimeOfProduction: Format(DateAdd("n",[ProductionTime],[StartTimeOfProduction]),"Medium Time")
Mar 7 '18 #27

P: 43
Perfect. Thank you so much. This is what the final Code ended up looking like.

Expand|Select|Wrap|Line Numbers
  1. EndTimeOfProduction: Format(Nz(DateAdd("n",Nz([ProductionTime]),[StartTimeOfProduction])),"Medium Time")
Mar 7 '18 #28

twinnyfo
Expert Mod 100+
P: 2,317
Glad I could hepp. Sorry it took so long to resolve this issue.
Mar 7 '18 #29

Post your reply

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