Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SUM time from other fields, insert into Totals formatted as hurs.

Discussion in 'Information Technology' started by Adnan, Jul 28, 2006.

  1. Adnan

    Adnan
    Expand Collapse
    Guest

    I have a Table that has these filed formatted as number
    1. TravelTime
    2. PreperationTime
    3. WorkTime
    4. Daily Hours
    I am looking for code that would SUM TravelTime, PreperationTime and
    WorkTime and insert them into DailyHours On After update event of each of
    1st, 2nd and 3rd filed and formatted as Hours.Minutes.

    I’ve managed to get this close but not formatted as hours.minutes
    This is what I did:
    On After updated of 1st, 2nd and 3rd filed I wrote this code:
    Daily Hours = TravelTime + PreperationTime + WorkTime
    The problem with this is that instead of displaying 2.10 it displays 1.70.
    Please assist?
    Adnan

    --
    Please post all your inquiries on this community so we can all benefit -
    Thank you!
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News In Pictures: Hundreds Of Youngsters Attend Sikh Summer Camp - Slough Express Breaking News Aug 13, 2016
    Sikh News Sikhs On 'The Daily Show' Have Perfect Response For People Who Assume They're Muslim Breaking News Apr 27, 2016
    Opinion "Sikh monument" irks Kenyan Kisumu preachers (UPDATED) Breaking News Feb 8, 2014
    SALDEF Be an Intern in Washington This Summer (SALDEF) Sikh Organisations Dec 18, 2013
    1984 Sikh riots: US court issues summons to Sonia History of Sikhism Sep 6, 2013

  3. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    1. You should not be storing the Daily Hours in the table. Instead you should
    be calculating it needed in queries, forms, and reports.

    2. You say it should show 2.10, but displays 1.70. What does? What numbers
    did you plug into the other three fields?
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "Adnan" wrote:

    > I have a Table that has these filed formatted as number
    > 1. TravelTime
    > 2. PreperationTime
    > 3. WorkTime
    > 4. Daily Hours
    > I am looking for code that would SUM TravelTime, PreperationTime and
    > WorkTime and insert them into DailyHours On After update event of each of
    > 1st, 2nd and 3rd filed and formatted as Hours.Minutes.
    >
    > I’ve managed to get this close but not formatted as hours.minutes
    > This is what I did:
    > On After updated of 1st, 2nd and 3rd filed I wrote this code:
    > Daily Hours = TravelTime + PreperationTime + WorkTime
    > The problem with this is that instead of displaying 2.10 it displays 1.70.
    > Please assist?
    > Adnan
    >
    > --
    > Please post all your inquiries on this community so we can all benefit -
    > Thank you!
     
  4. Adnan

    Adnan
    Expand Collapse
    Guest

    RE: SUM time from other fields, insert into Totals formatted as hu

    All four fields are formatted as number (#,##0.00) and with decimal places 2
    I’ve created a form with record source of this table with these fields.
    Now, I want the calculation to be performed on form. i.e: txtTravelTime’s
    on After update.
    Whatever formula/code it may be, it has to be in VBA so I can use on after
    update (on form)
    If this isn’t clean enough I would get you snapshot somewhow!
    Thank you Jerry!
    Adnan

    --
    Please post all your inquiries on this community so we can all benefit -
    Thank you!


    "Jerry Whittle" wrote:

    > 1. You should not be storing the Daily Hours in the table. Instead you should
    > be calculating it needed in queries, forms, and reports.
    >
    > 2. You say it should show 2.10, but displays 1.70. What does? What numbers
    > did you plug into the other three fields?
    > --
    > Jerry Whittle
    > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >
    >
    > "Adnan" wrote:
    >
    > > I have a Table that has these filed formatted as number
    > > 1. TravelTime
    > > 2. PreperationTime
    > > 3. WorkTime
    > > 4. Daily Hours
    > > I am looking for code that would SUM TravelTime, PreperationTime and
    > > WorkTime and insert them into DailyHours On After update event of each of
    > > 1st, 2nd and 3rd filed and formatted as Hours.Minutes.
    > >
    > > I’ve managed to get this close but not formatted as hours.minutes
    > > This is what I did:
    > > On After updated of 1st, 2nd and 3rd filed I wrote this code:
    > > Daily Hours = TravelTime + PreperationTime + WorkTime
    > > The problem with this is that instead of displaying 2.10 it displays 1.70.
    > > Please assist?
    > > Adnan
    > >
    > > --
    > > Please post all your inquiries on this community so we can all benefit -
    > > Thank you!
     
  5. Adnan

    Adnan
    Expand Collapse
    Guest

    RE: SUM time from other fields, insert into Totals formatted as hu

    Jerry --- this should make myself clearer on what I’m looking for:
    http://punaime.itgo.com/msaccess1.html
    Once again, thank you!
    Adnan

    --
    Please post all your inquiries on this community so we can all benefit -
    Thank you!


    "Jerry Whittle" wrote:

    > 1. You should not be storing the Daily Hours in the table. Instead you should
    > be calculating it needed in queries, forms, and reports.
    >
    > 2. You say it should show 2.10, but displays 1.70. What does? What numbers
    > did you plug into the other three fields?
    > --
    > Jerry Whittle
    > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >
    >
    > "Adnan" wrote:
    >
    > > I have a Table that has these filed formatted as number
    > > 1. TravelTime
    > > 2. PreperationTime
    > > 3. WorkTime
    > > 4. Daily Hours
    > > I am looking for code that would SUM TravelTime, PreperationTime and
    > > WorkTime and insert them into DailyHours On After update event of each of
    > > 1st, 2nd and 3rd filed and formatted as Hours.Minutes.
    > >
    > > I’ve managed to get this close but not formatted as hours.minutes
    > > This is what I did:
    > > On After updated of 1st, 2nd and 3rd filed I wrote this code:
    > > Daily Hours = TravelTime + PreperationTime + WorkTime
    > > The problem with this is that instead of displaying 2.10 it displays 1.70.
    > > Please assist?
    > > Adnan
    > >
    > > --
    > > Please post all your inquiries on this community so we can all benefit -
    > > Thank you!
     
  6. JK

    JK
    Expand Collapse
    Guest

    Re: SUM time from other fields, insert into Totals formatted as hu

    Adnan,

    Access keeps time as numbers whereas 1=day, thus your input is 0.1 day
    (2h-24m) +0.5 day (12h-00m) + 1.1 day (1d-2h-24m) =1.7 days (1d-16h-48m).

    It appears that you meant 0.1=10m + 0.5=50m + 1.10=1h-10m (sum=2.1 =
    2h-10m).

    In order to get the correct result :

    Specify the *all* the fields as date/time fields with a time format (hh:nn
    and Decimal place =Auto) When you sum them also into a date/time field
    (daily Hours) you will get the correct result. This will work fine
    *provided that* you *never* exceed 23h=59m in daily hours (or any other
    date/time field with time format for that matter).

    Bear in mind the when you enter time only Access will default to day 0 (Dec
    30,1899) and when the sum exceeds 23h-59m the actual number that is stored
    is on Dec 31 1899. e.g
    if the total is say, 26h-20m the actual result you will see is 01:20 and not
    26:20 because Access will store Dec 31, 1899 01:20, Jan 1, 1900 for times in
    excess of 47h-59m
    etc.

    Regards/JK






    "Adnan" <Adnan@discussions.microsoft.com> wrote in message
    news:D5E89D59-FC7E-4C7B-955A-ADF65AB73292@microsoft.com...
    > Jerry --- this should make myself clearer on what I'm looking for:
    > http://punaime.itgo.com/msaccess1.html
    > Once again, thank you!
    > Adnan
    >
    > --
    > Please post all your inquiries on this community so we can all benefit -
    > Thank you!
    >
    >
    > "Jerry Whittle" wrote:
    >
    >> 1. You should not be storing the Daily Hours in the table. Instead you
    >> should
    >> be calculating it needed in queries, forms, and reports.
    >>
    >> 2. You say it should show 2.10, but displays 1.70. What does? What
    >> numbers
    >> did you plug into the other three fields?
    >> --
    >> Jerry Whittle
    >> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >>
    >>
    >> "Adnan" wrote:
    >>
    >> > I have a Table that has these filed formatted as number
    >> > 1. TravelTime
    >> > 2. PreperationTime
    >> > 3. WorkTime
    >> > 4. Daily Hours
    >> > I am looking for code that would SUM TravelTime, PreperationTime and
    >> > WorkTime and insert them into DailyHours On After update event of each
    >> > of
    >> > 1st, 2nd and 3rd filed and formatted as Hours.Minutes.
    >> >
    >> > I've managed to get this close but not formatted as hours.minutes
    >> > This is what I did:
    >> > On After updated of 1st, 2nd and 3rd filed I wrote this code:
    >> > Daily Hours = TravelTime + PreperationTime + WorkTime
    >> > The problem with this is that instead of displaying 2.10 it displays
    >> > 1.70.
    >> > Please assist?
    >> > Adnan
    >> >
    >> > --
    >> > Please post all your inquiries on this community so we can all
    >> > benefit -
    >> > Thank you!
     
  7. Adnan

    Adnan
    Expand Collapse
    Guest

    Re: SUM time from other fields, insert into Totals formatted as hu

    This is almost exactly what I’m looking for --- the only issue is this: “if
    the total is say, 26h-20m the actual result you will see is 01:20 and not
    26:20 because Access will store Dec 31, 1899 01:20, Jan 1, 1900 for times in
    excess of 47h-59m etc.â€

    How do I make this exceed 24h:20m --- or will there be any other solution?
    Thank you, Adnan

    --
    Please post all your inquiries on this community so we can all benefit -
    Thank you!


    "JK" wrote:

    > Adnan,
    >
    > Access keeps time as numbers whereas 1=day, thus your input is 0.1 day
    > (2h-24m) +0.5 day (12h-00m) + 1.1 day (1d-2h-24m) =1.7 days (1d-16h-48m).
    >
    > It appears that you meant 0.1=10m + 0.5=50m + 1.10=1h-10m (sum=2.1 =
    > 2h-10m).
    >
    > In order to get the correct result :
    >
    > Specify the *all* the fields as date/time fields with a time format (hh:nn
    > and Decimal place =Auto) When you sum them also into a date/time field
    > (daily Hours) you will get the correct result. This will work fine
    > *provided that* you *never* exceed 23h=59m in daily hours (or any other
    > date/time field with time format for that matter).
    >
    > Bear in mind the when you enter time only Access will default to day 0 (Dec
    > 30,1899) and when the sum exceeds 23h-59m the actual number that is stored
    > is on Dec 31 1899. e.g
    > if the total is say, 26h-20m the actual result you will see is 01:20 and not
    > 26:20 because Access will store Dec 31, 1899 01:20, Jan 1, 1900 for times in
    > excess of 47h-59m
    > etc.
    >
    > Regards/JK
    >
    >
    >
    >
    >
    >
    > "Adnan" <Adnan@discussions.microsoft.com> wrote in message
    > news:D5E89D59-FC7E-4C7B-955A-ADF65AB73292@microsoft.com...
    > > Jerry --- this should make myself clearer on what I'm looking for:
    > > http://punaime.itgo.com/msaccess1.html
    > > Once again, thank you!
    > > Adnan
    > >
    > > --
    > > Please post all your inquiries on this community so we can all benefit -
    > > Thank you!
    > >
    > >
    > > "Jerry Whittle" wrote:
    > >
    > >> 1. You should not be storing the Daily Hours in the table. Instead you
    > >> should
    > >> be calculating it needed in queries, forms, and reports.
    > >>
    > >> 2. You say it should show 2.10, but displays 1.70. What does? What
    > >> numbers
    > >> did you plug into the other three fields?
    > >> --
    > >> Jerry Whittle
    > >> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    > >>
    > >>
    > >> "Adnan" wrote:
    > >>
    > >> > I have a Table that has these filed formatted as number
    > >> > 1. TravelTime
    > >> > 2. PreperationTime
    > >> > 3. WorkTime
    > >> > 4. Daily Hours
    > >> > I am looking for code that would SUM TravelTime, PreperationTime and
    > >> > WorkTime and insert them into DailyHours On After update event of each
    > >> > of
    > >> > 1st, 2nd and 3rd filed and formatted as Hours.Minutes.
    > >> >
    > >> > I've managed to get this close but not formatted as hours.minutes
    > >> > This is what I did:
    > >> > On After updated of 1st, 2nd and 3rd filed I wrote this code:
    > >> > Daily Hours = TravelTime + PreperationTime + WorkTime
    > >> > The problem with this is that instead of displaying 2.10 it displays
    > >> > 1.70.
    > >> > Please assist?
    > >> > Adnan
    > >> >
    > >> > --
    > >> > Please post all your inquiries on this community so we can all
    > >> > benefit -
    > >> > Thank you!

    >
    >
    >
     
  8. Adnan

    Adnan
    Expand Collapse
    Guest

    Re: SUM time from other fields, insert into Totals formatted as hu

    ....anyone, anything, please?

    --
    Please post all your inquiries on this community so we can all benefit -
    Thank you!


    "JK" wrote:

    > Adnan,
    >
    > Access keeps time as numbers whereas 1=day, thus your input is 0.1 day
    > (2h-24m) +0.5 day (12h-00m) + 1.1 day (1d-2h-24m) =1.7 days (1d-16h-48m).
    >
    > It appears that you meant 0.1=10m + 0.5=50m + 1.10=1h-10m (sum=2.1 =
    > 2h-10m).
    >
    > In order to get the correct result :
    >
    > Specify the *all* the fields as date/time fields with a time format (hh:nn
    > and Decimal place =Auto) When you sum them also into a date/time field
    > (daily Hours) you will get the correct result. This will work fine
    > *provided that* you *never* exceed 23h=59m in daily hours (or any other
    > date/time field with time format for that matter).
    >
    > Bear in mind the when you enter time only Access will default to day 0 (Dec
    > 30,1899) and when the sum exceeds 23h-59m the actual number that is stored
    > is on Dec 31 1899. e.g
    > if the total is say, 26h-20m the actual result you will see is 01:20 and not
    > 26:20 because Access will store Dec 31, 1899 01:20, Jan 1, 1900 for times in
    > excess of 47h-59m
    > etc.
    >
    > Regards/JK
    >
    >
    >
    >
    >
    >
    > "Adnan" <Adnan@discussions.microsoft.com> wrote in message
    > news:D5E89D59-FC7E-4C7B-955A-ADF65AB73292@microsoft.com...
    > > Jerry --- this should make myself clearer on what I'm looking for:
    > > http://punaime.itgo.com/msaccess1.html
    > > Once again, thank you!
    > > Adnan
    > >
    > > --
    > > Please post all your inquiries on this community so we can all benefit -
    > > Thank you!
    > >
    > >
    > > "Jerry Whittle" wrote:
    > >
    > >> 1. You should not be storing the Daily Hours in the table. Instead you
    > >> should
    > >> be calculating it needed in queries, forms, and reports.
    > >>
    > >> 2. You say it should show 2.10, but displays 1.70. What does? What
    > >> numbers
    > >> did you plug into the other three fields?
    > >> --
    > >> Jerry Whittle
    > >> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    > >>
    > >>
    > >> "Adnan" wrote:
    > >>
    > >> > I have a Table that has these filed formatted as number
    > >> > 1. TravelTime
    > >> > 2. PreperationTime
    > >> > 3. WorkTime
    > >> > 4. Daily Hours
    > >> > I am looking for code that would SUM TravelTime, PreperationTime and
    > >> > WorkTime and insert them into DailyHours On After update event of each
    > >> > of
    > >> > 1st, 2nd and 3rd filed and formatted as Hours.Minutes.
    > >> >
    > >> > I've managed to get this close but not formatted as hours.minutes
    > >> > This is what I did:
    > >> > On After updated of 1st, 2nd and 3rd filed I wrote this code:
    > >> > Daily Hours = TravelTime + PreperationTime + WorkTime
    > >> > The problem with this is that instead of displaying 2.10 it displays
    > >> > 1.70.
    > >> > Please assist?
    > >> > Adnan
    > >> >
    > >> > --
    > >> > Please post all your inquiries on this community so we can all
    > >> > benefit -
    > >> > Thank you!

    >
    >
    >
     
  9. JK

    JK
    Expand Collapse
    Guest

    Re: SUM time from other fields, insert into Totals formatted as hu

    Adnan,

    The only way I know, other here may know better, is to write a function that
    take the number and return a string - make the total (or any field that may
    exceed 23h:59m) invisible and display the string generated by the function.

    Regards /JK





    "Adnan" <te_adnani@hotmail.com(donotspam)> wrote in message
    news:66F2C64A-E5C9-40F7-AC35-EE05B53A50E1@microsoft.com...
    > This is almost exactly what I'm looking for --- the only issue is this:
    > "if
    > the total is say, 26h-20m the actual result you will see is 01:20 and not
    > 26:20 because Access will store Dec 31, 1899 01:20, Jan 1, 1900 for times
    > in
    > excess of 47h-59m etc."
    >
    > How do I make this exceed 24h:20m --- or will there be any other solution?
    > Thank you, Adnan
    >
    > --
    > Please post all your inquiries on this community so we can all benefit -
    > Thank you!
    >
    >
    > "JK" wrote:
    >
    >> Adnan,
    >>
    >> Access keeps time as numbers whereas 1=day, thus your input is 0.1 day
    >> (2h-24m) +0.5 day (12h-00m) + 1.1 day (1d-2h-24m) =1.7 days (1d-16h-48m).
    >>
    >> It appears that you meant 0.1=10m + 0.5=50m + 1.10=1h-10m (sum=2.1 =
    >> 2h-10m).
    >>
    >> In order to get the correct result :
    >>
    >> Specify the *all* the fields as date/time fields with a time format
    >> (hh:nn
    >> and Decimal place =Auto) When you sum them also into a date/time field
    >> (daily Hours) you will get the correct result. This will work fine
    >> *provided that* you *never* exceed 23h=59m in daily hours (or any other
    >> date/time field with time format for that matter).
    >>
    >> Bear in mind the when you enter time only Access will default to day 0
    >> (Dec
    >> 30,1899) and when the sum exceeds 23h-59m the actual number that is
    >> stored
    >> is on Dec 31 1899. e.g
    >> if the total is say, 26h-20m the actual result you will see is 01:20 and
    >> not
    >> 26:20 because Access will store Dec 31, 1899 01:20, Jan 1, 1900 for times
    >> in
    >> excess of 47h-59m
    >> etc.
    >>
    >> Regards/JK
    >>
    >>
    >>
    >>
    >>
    >>
    >> "Adnan" <Adnan@discussions.microsoft.com> wrote in message
    >> news:D5E89D59-FC7E-4C7B-955A-ADF65AB73292@microsoft.com...
    >> > Jerry --- this should make myself clearer on what I'm looking for:
    >> > http://punaime.itgo.com/msaccess1.html
    >> > Once again, thank you!
    >> > Adnan
    >> >
    >> > --
    >> > Please post all your inquiries on this community so we can all
    >> > benefit -
    >> > Thank you!
    >> >
    >> >
    >> > "Jerry Whittle" wrote:
    >> >
    >> >> 1. You should not be storing the Daily Hours in the table. Instead you
    >> >> should
    >> >> be calculating it needed in queries, forms, and reports.
    >> >>
    >> >> 2. You say it should show 2.10, but displays 1.70. What does? What
    >> >> numbers
    >> >> did you plug into the other three fields?
    >> >> --
    >> >> Jerry Whittle
    >> >> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >> >>
    >> >>
    >> >> "Adnan" wrote:
    >> >>
    >> >> > I have a Table that has these filed formatted as number
    >> >> > 1. TravelTime
    >> >> > 2. PreperationTime
    >> >> > 3. WorkTime
    >> >> > 4. Daily Hours
    >> >> > I am looking for code that would SUM TravelTime, PreperationTime and
    >> >> > WorkTime and insert them into DailyHours On After update event of
    >> >> > each
    >> >> > of
    >> >> > 1st, 2nd and 3rd filed and formatted as Hours.Minutes.
    >> >> >
    >> >> > I've managed to get this close but not formatted as hours.minutes
    >> >> > This is what I did:
    >> >> > On After updated of 1st, 2nd and 3rd filed I wrote this code:
    >> >> > Daily Hours = TravelTime + PreperationTime + WorkTime
    >> >> > The problem with this is that instead of displaying 2.10 it displays
    >> >> > 1.70.
    >> >> > Please assist?
    >> >> > Adnan
    >> >> >
    >> >> > --
    >> >> > Please post all your inquiries on this community so we can all
    >> >> > benefit -
    >> >> > Thank you!

    >>
    >>
    >>
     

Share This Page