Subtracting Minutes from a Date/Time Field

Ashleigh

New member
Local time
Today, 10:59
Joined
Feb 27, 2012
Messages
8
Morning all, I am fairly new to Access and am currently trying to subtract minutes. I have two tables One contains the Field "Activity Start Time", another contains the field "Journey Time". in a query I want to calculate the departure time, i.e. Start Time minus Journey Time. how can I do this please.
Many thanks in advance for any help
 
This query retrieves the difference between two time fields in a table and formats the result as hours, minutes & seconds.
Code:
SELECT Format((StopTime - StartTime) ,"hh:mm:ss")AS Duration FROM TableName;
I don't quite follow your logic of calculating departure time when you already have the start time, but that's by-the-by.:)
 
This query retrieves the difference between two time fields in a table and formats the result as hours, minutes & seconds.
Code:
SELECT Format((StopTime - StartTime) ,"hh:mm:ss")AS Duration FROM TableName;
I don't quite follow your logic of calculating departure time when you already have the start time, but that's by-the-by.:)

Thank you for replying quickly. The reason for the calculation is that I am doing an Order Database for Accommodation and Activities, from which I can produce an Itinerary. I will know the start time of an activity and the journey time to get to the Venue, so I need to be able to advise when the customer should start the journey.

I think Im being rather thick and I apologise for that.... An activity may start at 11am (stored in Activity Start Time Field), it takes 45 minutes to get there (stored in Journey Time field), so they need to leave at 10.15am which would be my departure time. Which parts of the query above do I change to my field names?

Sorry for being daft but its getting late and my heads not working properly.
Thank you again.
 
My head stopped working years ago!:)
In my example, you can substitute [Activity Start Time] for StopTime and [Journey Time] for StartTime. You need the square brackets because the names have embedded spaces.
As you only want hours and minutes for the departure time, you can change the format statement mask to "hh:mm".
 
I should add that the start time field is in the 'Order Details' Table and the Journey Time field is in the 'Venue' table.
 
OK, so what is the relationship between the two tables? How do you determine which records to get the two times from?
I won't respond further on this tonight, but if no-one else has chipped in by tomorrow morning, I'll do so then.
 
Thank you for looking at my problem.

I have used the Northwind template and have these tables, Orders, Order Details, Products, Suppliers, Venue Details amongst others. Order details table incl Field "start time" of an activity'. Venue Table inc the field "Journey Time".

An Itinerary Report gets its details from an Itinerary query with the orders, suppliers and venue tables' within that query I'm trying to calculate what time they would need to leave to get to a venue by "start time". As above I know the start time and journey time.

I've tried adding a column in the query with the following criteria(?), "Leave for venue: Start Time - Journey Time". With a Start Time of 11am and a Journey Time of 45m I've managed to get the result either 11am (the same as start time) or 11.45 (+ 45 mins)

Does this give you any better idea of what I'm talking about?
 
I had a look at NorthWind, but it doesn't have the Venue table, so that must be one you have added yourself?
I have created a pair of dummy tables with a query to show the basics of calculating time difference between related fields in the two tables. The result is in the attached zip file.
In summary, table tTimeTest1 simply has an autonumbered key plus event start time (t1StartTime) and table tTimeTest2 has a key plus journey time (t2JourneyTime) and a link field (t2Link) which contains the key of the related record in t1TimeTest.
The query is as follows:
Code:
SELECT Format([t1StartTime]-[t2JourneyTime],"Short Time") AS qDepartureTime FROM tTimeTest2 INNER JOIN tTimeTest1 ON tTimeTest2.t2Link=tTimeTest1.t1Key;
Note the INNER JOIN phrase in the SQL - that tells Access how the tables are linked together.
The second page in the PDF shows the table data with the query results. Note that record 2 in table 1 is linked to record 1 of table 2, so the calculations are 11:30 - 0:45 = 10:45 and 11:00 - 1:10 - 09:50, which is correct. Your results suggest either a problem with the data or/and the calculation.
 

Attachments

Users who are viewing this thread

Back
Top Bottom