A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Problem is sum hours, because when cross 24h start in zero



 
 
Thread Tools Display Modes
  #1  
Old November 23rd, 2006, 04:48 PM posted to microsoft.public.access.queries
Marco
external usenet poster
 
Posts: 535
Default Problem is sum hours, because when cross 24h start in zero

Hello. How can I sum a total of hours. I'm having this problem. When the
total of hours cross 24h it restart, like it was passing to another day.
Exemple: 18.00H+10.00h appears as 04.00h. and I wnat to appear 26.00h.

Con should I format this? In excel I format as [h] and it works, but it's
not working here.

Regards,
Marco
  #2  
Old November 23rd, 2006, 04:57 PM posted to microsoft.public.access.queries
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Problem is sum hours, because when cross 24h start in zero

"Marco" wrote in message
...
Hello. How can I sum a total of hours. I'm having this problem. When the
total of hours cross 24h it restart, like it was passing to another day.
Exemple: 18.00H+10.00h appears as 04.00h. and I wnat to appear 26.00h.

Con should I format this? In excel I format as [h] and it works, but it's
not working here.


Databases do not have a data type for storing *amounts of time*. The DateTime
field is intended for "points in time". In a database 18:00 means 6 PM, not 18
hours and zero minutes.

Since many database systems store DateTimes under the covers as numeric values
you can sometimes get way with using these fields as if they were durations, but
as you have seen that blows up when you cross 24 hour boundaries.

The recommended approach is to store your durations as regular numbers like the
number of minutes or the number of seconds using an Integer or Long Integer
field. Then you can do all kinds of math on them easily. Final results can for
display purposes be converted back to an hours and minutes format.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com




  #3  
Old November 23rd, 2006, 05:19 PM posted to microsoft.public.access.queries
Marco
external usenet poster
 
Posts: 535
Default Problem is sum hours, because when cross 24h start in zero

Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.

Regards,
Marco



"Rick Brandt" wrote:

"Marco" wrote in message
...
Hello. How can I sum a total of hours. I'm having this problem. When the
total of hours cross 24h it restart, like it was passing to another day.
Exemple: 18.00H+10.00h appears as 04.00h. and I wnat to appear 26.00h.

Con should I format this? In excel I format as [h] and it works, but it's
not working here.


Databases do not have a data type for storing *amounts of time*. The DateTime
field is intended for "points in time". In a database 18:00 means 6 PM, not 18
hours and zero minutes.

Since many database systems store DateTimes under the covers as numeric values
you can sometimes get way with using these fields as if they were durations, but
as you have seen that blows up when you cross 24 hour boundaries.

The recommended approach is to store your durations as regular numbers like the
number of minutes or the number of seconds using an Integer or Long Integer
field. Then you can do all kinds of math on them easily. Final results can for
display purposes be converted back to an hours and minutes format.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com





  #4  
Old November 23rd, 2006, 05:32 PM posted to microsoft.public.access.queries
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Problem is sum hours, because when cross 24h start in zero

"Marco" wrote in message
...
Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.


I fail to see how you can go from a sum of hours equaling 45,75399583 hours to
the number 1098.

Is the comma the decimal point character in your regional settings?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


  #5  
Old November 23rd, 2006, 06:44 PM posted to microsoft.public.access.queries
Marco
external usenet poster
 
Posts: 535
Default Problem is sum hours, because when cross 24h start in zero

Yes. the comma the decimal point character in my regional settings.

Can you help me?

Marco



"Rick Brandt" wrote:

"Marco" wrote in message
...
Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.


I fail to see how you can go from a sum of hours equaling 45,75399583 hours to
the number 1098.

Is the comma the decimal point character in your regional settings?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



  #6  
Old November 23rd, 2006, 06:49 PM posted to microsoft.public.access.queries
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Problem is sum hours, because when cross 24h start in zero

"Marco" wrote in message
...
Yes. the comma the decimal point character in my regional settings.

Can you help me?


So you have a total sum of hours with a value of 45,75399583, which I'm
interpreting to be a bit over 45 and 3/4 hours. Just how do you want that
displayed?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


  #7  
Old November 23rd, 2006, 09:12 PM posted to microsoft.public.access.queries
Marco
external usenet poster
 
Posts: 535
Default Problem is sum hours, because when cross 24h start in zero

Try to past this number into a excel sheet. Then format this cell, goto
Format cells an in custom use this kind of format [h] it will convert the
numbers into hours.

Marco






"Rick Brandt" wrote:

"Marco" wrote in message
...
Yes. the comma the decimal point character in my regional settings.

Can you help me?


So you have a total sum of hours with a value of 45,75399583, which I'm
interpreting to be a bit over 45 and 3/4 hours. Just how do you want that
displayed?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



  #8  
Old November 23rd, 2006, 11:18 PM posted to microsoft.public.access.queries
John Vinson
external usenet poster
 
Posts: 4,033
Default Problem is sum hours, because when cross 24h start in zero

On Thu, 23 Nov 2006 08:19:01 -0800, Marco
wrote:

Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.


Try

Fix([Total]) & ":" & Format(60*[Total] - Fix([Total]), "00")


John W. Vinson[MVP]
  #9  
Old November 24th, 2006, 12:11 PM posted to microsoft.public.access.queries
Marco
external usenet poster
 
Posts: 535
Default Problem is sum hours, because when cross 24h start in zero

Hi. Well with that code you I’m approaching of what I need. But instead of
counting 83h counts 6:367h.

Any idea?

Regards,
Marco


"John Vinson" wrote:

On Thu, 23 Nov 2006 08:19:01 -0800, Marco
wrote:

Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.


Try

Fix([Total]) & ":" & Format(60*[Total] - Fix([Total]), "00")


John W. Vinson[MVP]

  #10  
Old November 24th, 2006, 02:06 PM posted to microsoft.public.access.queries
Marco
external usenet poster
 
Posts: 535
Default Problem is sum hours, because when cross 24h start in zero

John,

I've got this number and I want that appears 149.

Regards,
Marco




"John Vinson" wrote:

On Thu, 23 Nov 2006 08:19:01 -0800, Marco
wrote:

Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.


Try

Fix([Total]) & ":" & Format(60*[Total] - Fix([Total]), "00")


John W. Vinson[MVP]

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.