Add time in access using 2 fields

[DJRhino1175]

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

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

Thanks for the help,

DJRhino

[twinnyfo]

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:

[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!

[DJRhino1175]

I put this as my Code:

EndTimeOfProduction: DateAdd("n",[ProductionTime],[StartTimeOfProduction])

Now I get #Name?

[twinnyfo]

Where are you putting this code? Is this on a Form? Are you running script from a VBA module behind a Form? Something else?

[DJRhino1175]

I added this to a query that is feeding my form.

[twinnyfo]

Very strange....

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

What data types are you using for [ProductionTime] and [StartTimeOfProd uction]? And these are fields in your Table?

[DJRhino1175]

[ProductionTime] is a calculated field from info entered into the table. [EndtimeOfproduc tion] is in the table currently entered manually. in medium time format

[DJRhino1175]

This is how [ProductionTime] is in the query:

ProductionTime: ([GoodMeters]/[CalcLineSpeed]+(Nz([Scrap])/[CalcLineSpeed]))

[GoodMeters] & CalcLineSpeed] are also calculated fields.

[twinnyfo]

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.

[twinnyfo]

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.

[DJRhino1175]

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 [startTimeOfProd uction] which is in medium time format (6:30 am)

Hope this clears it up a little.

[twinnyfo]

You say

I want to take those minutes and added them to the [startTimeOfProd uction] which is in medium time format (6:30 am)

But, is [startTimeOfProd uction] 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.....)

[DJRhino1175]

StartTimeOfProd uction = Date/Time in the table I'm pulling from

[twinnyfo]

Bad news: I've recalculated everything just like you have described and my Query still works:

[IMGNOTHUMB]https://bytes.com/attachments/attachment/9387d1520362870/djrhino.jpg[/IMGNOTHUMB]

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
FROM tblDJRhino;

Not sure what the issue is here..... Again, we gotta be missing something obvious.

[DJRhino1175]

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.