Need Help with Time Format

  • Thread starter Thread starter DaGoon
  • Start date Start date
D

DaGoon

To All,

Can I please trouble the group for some assistance?

I have a number field named "stoplength" in a table that I convert to
time with an append query as follows:

[stoplength]*60/1440

The append query adds the above results to the same table but in a
number field named "mindown"

When I display the sum of "mindown" on a form as follows:

="Total Downtime in Hours:Minutes - " & Format(Sum([MinDown]),"hh:mm")

The problem arises when the total downtime exceeds 24:00. Instead of
going to 25:34 it will just start again at 1:00.

The funny thing is that when I add a chart to this form the data
displays properly when it goes past 24:00. For the chart axis and the
data labels I am using the following format for the numbers: time
category and 37:30:55 for the type.

So, my question is, what is the syntax for the 37:30:55 type that
seems to work in the chart? Could it be as easy as using:

="Total Downtime in Hours:Minutes - " &
Format(Sum([MinDown]),"37:30:55")

Or, is there a custom format that I should be using?

Any and all help is greatly appreciated!
 
A time only displays time - so when you go past one day, the clock starts over
(the date portion gets incremented by one). Access date time will never be more
than 23:59:59

If you are charting this, then you need it stored as a numeric value, however,
if you wish to see this as hh:mm then you will have to use a string.

String Value:
Sum(MinDown*1440)\60 & ":" & Format(Sum(MinDown*1440) Mod 60,"00)

Number value for plotting
SUM(MinDown*1440)/60
 
John,

I'm a beginner when it come to Access and VB.

When you state use a string. How do I accomplish this? Is it as easy
as adding the following to the control source of my textbox:

Sum(MinDown*1440)\60 & ":" & Format(Sum(MinDown*1440) Mod 60,"00

If I'm incorrect can you please elaborate?

Thanks for your help!


A time only displays time - so when you go past one day, the clock starts over
(the date portion gets incremented by one). Access date time will never be more
than 23:59:59

If you are charting this, then you need it stored as a numeric value, however,
if you wish to see this as hh:mm then you will have to use a string.

String Value:
Sum(MinDown*1440)\60 & ":" & Format(Sum(MinDown*1440) Mod 60,"00)

Number value for plotting
SUM(MinDown*1440)/60
To All,

Can I please trouble the group for some assistance?

I have a number field named "stoplength" in a table that I convert to
time with an append query as follows:

[stoplength]*60/1440

The append query adds the above results to the same table but in a
number field named "mindown"

When I display the sum of "mindown" on a form as follows:

="Total Downtime in Hours:Minutes - " & Format(Sum([MinDown]),"hh:mm")

The problem arises when the total downtime exceeds 24:00. Instead of
going to 25:34 it will just start again at 1:00.

The funny thing is that when I add a chart to this form the data
displays properly when it goes past 24:00. For the chart axis and the
data labels I am using the following format for the numbers: time
category and 37:30:55 for the type.

So, my question is, what is the syntax for the 37:30:55 type that
seems to work in the chart? Could it be as easy as using:

="Total Downtime in Hours:Minutes - " &
Format(Sum([MinDown]),"37:30:55")

Or, is there a custom format that I should be using?

Any and all help is greatly appreciated!
Click to expand...
Click to expand...
 
String Value:
Sum(MinDown*1440)\60 & ":" & Format(Sum(MinDown*1440) Mod 60,"00")

I seem to have left off the Closing quote mark in my original response.

You can place that as the control source of your text box as long as your
text box is not named MinDown. If it is then try changing the name of the
textbox. You can also use that formula in a query and just refer to the
queries field as the control source for the textbox.
DaGoon said:
John,

I'm a beginner when it come to Access and VB.

When you state use a string. How do I accomplish this? Is it as easy
as adding the following to the control source of my textbox:

Sum(MinDown*1440)\60 & ":" & Format(Sum(MinDown*1440) Mod 60,"00

If I'm incorrect can you please elaborate?

Thanks for your help!


A time only displays time - so when you go past one day, the clock starts
over
(the date portion gets incremented by one). Access date time will never
be more
than 23:59:59

If you are charting this, then you need it stored as a numeric value,
however,
if you wish to see this as hh:mm then you will have to use a string.

String Value:
Sum(MinDown*1440)\60 & ":" & Format(Sum(MinDown*1440) Mod 60,"00)

Number value for plotting
SUM(MinDown*1440)/60
To All,

Can I please trouble the group for some assistance?

I have a number field named "stoplength" in a table that I convert to
time with an append query as follows:

[stoplength]*60/1440

The append query adds the above results to the same table but in a
number field named "mindown"

When I display the sum of "mindown" on a form as follows:

="Total Downtime in Hours:Minutes - " & Format(Sum([MinDown]),"hh:mm")

The problem arises when the total downtime exceeds 24:00. Instead of
going to 25:34 it will just start again at 1:00.

The funny thing is that when I add a chart to this form the data
displays properly when it goes past 24:00. For the chart axis and the
data labels I am using the following format for the numbers: time
category and 37:30:55 for the type.

So, my question is, what is the syntax for the 37:30:55 type that
seems to work in the chart? Could it be as easy as using:

="Total Downtime in Hours:Minutes - " &
Format(Sum([MinDown]),"37:30:55")

Or, is there a custom format that I should be using?

Any and all help is greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top