Adding time date fields

[Joe Birt]

I am trying to design a schedule using acces and mail
merge.
The problem I have at the moment is I cannot get two Times
to add up in a 3rd column
i.e
Column a + Column b = Column c

The times for each record in the database will be different

Any ideas

Thanks in anticpation

 

[Allen Browne]

Not really clear about your data. Do columns A and B contain Date/Time data
(formatted to Short Time), and you want the calculated column to be the sum
of the hours and minutes of the other 2 columns?

If so, there will be a problem if A and B add up to more than 24 hours.
Column C will appear to lose the extra day, or to produce a weird result
such as 1am on December 30, 1899.

Typing this line into the Field row of the query design grid should give you
the calculated column C showing hours and minutes:
C: (DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, ) \ 60) &
Format((DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, ) Mod 60),
"\:00")

For an explanation of how it works, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

If that's not what you were asking, please post back.

 

[Joe Birt]

Thanks for that, it should work. Just so you know I am
designing an Aircraft Schedule with Passenger Details and
destinations. The times will not exceed 24 hrs, the max
time we fly in one leg is 2.5 hrs. If I may I will contact
you again to let you know if it worked as I wanted

Joe

-----Original Message-----
Not really clear about your data. Do columns A and B contain Date/Time data
(formatted to Short Time), and you want the calculated column to be the sum
of the hours and minutes of the other 2 columns?

If so, there will be a problem if A and B add up to more than 24 hours.
Column C will appear to lose the extra day, or to produce a weird result
such as 1am on December 30, 1899.

Typing this line into the Field row of the query design grid should give you
the calculated column C showing hours and minutes:
C: (DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, ) \ 60) &
Format((DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, ) Mod 60),
"\:00")

For an explanation of how it works, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

If that's not what you were asking, please post back.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to design a schedule using acces and mail
merge.
The problem I have at the moment is I cannot get two Times
to add up in a 3rd column
i.e
Column a + Column b = Column c

The times for each record in the database will be different

Any ideas

Thanks in anticpation

.

 

[Joe Birt]

-----Original Message-----
Not really clear about your data. Do columns A and B contain Date/Time data
(formatted to Short Time), and you want the calculated column to be the sum
of the hours and minutes of the other 2 columns?

If so, there will be a problem if A and B add up to more than 24 hours.
Column C will appear to lose the extra day, or to produce a weird result
such as 1am on December 30, 1899.

Typing this line into the Field row of the query design grid should give you
the calculated column C showing hours and minutes:
C: (DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, ) \ 60) &
Format((DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, ) Mod 60),
"\:00")

For an explanation of how it works, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

If that's not what you were asking, please post back.

It might be me but I cant get it to work,
All times are short Times.
I copy and paste your solution into the Field Row, but I
am unsure if I should use C:
I get different errors

Sorry for the trouble Joe

 

[Allen Browne]

Just to be clear, we are talking about working in query design view.

Try building up the expression a bit at a time.
The "C" is the alias for the field name: use any name you like.

Try just this in a fresh column (Field row):
DateDiff("n", #0:00#, [A])
That should return the number of minutes in field A since midnight.

Once that's working, try this in the next column:
DateDiff("n", #0:00#, )
That's the number fo minutes since midnight in column B.

In the next column:
DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, )
That's the sum of the two.

Next column:
(DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, )) \ 60
That's the whole number of hours.

Next column:
(DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, )) Mod 60
That's the whole minutes.

Next column:
((DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, )) \ 60) &
Format((DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, )) Mod 60,
"\:00")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

-----Original Message-----
Not really clear about your data. Do columns A and B contain Date/Time data
(formatted to Short Time), and you want the calculated column to be the sum
of the hours and minutes of the other 2 columns?

If so, there will be a problem if A and B add up to more than 24 hours.
Column C will appear to lose the extra day, or to produce a weird result
such as 1am on December 30, 1899.

Typing this line into the Field row of the query design grid should give you
the calculated column C showing hours and minutes:
C: (DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, ) \ 60) &
Format((DateDiff("n", #0:00#, [A]) + DateDiff("n", #0:00#, ) Mod 60),
"\:00")

For an explanation of how it works, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

If that's not what you were asking, please post back.

It might be me but I cant get it to work,
All times are short Times.
I copy and paste your solution into the Field Row, but I
am unsure if I should use C:
I get different errors

Sorry for the trouble Joe