Remove leading zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following as a control source in the group footer section of a
report:
=SecondsToTime(Sum(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
This produces 7:11:04 or 22:44:21 as examples - how can I get a zero to
appear in front of 7:11:04 so it is hh:mm:ss format?

Alternatively, the following control source in the detail section
=TimeSerial(0,0,(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
gives the hh:mm:ss with a leading zero where necessary. Is it easier to
remove the zero here or put it in the group footer control source?

I don't mind which, Ijust want them the same.
 
In your SecondsToTime function, which I assume is returning a string, you
would use the Format function to ensure the leading zero.

Not knowing how the function produces its value, I can't give you any
specific suggestions. If you post the code, then we should be able to help
you.
 
This is in a report.
I am being sent an amount of time in hh:mm:ss which I am storing as
date/time format as that is how it gets to me. This is displayed in the
detail section of my report. I convert it to seconds using the TimeToSeconds
function (this is hidden on the report) then use that to sum up in the group
footer. I then use the SecondsToTime function to convert back to hh:ss:mm for
display and don't get the leading zero where the total hours is <10.
Does this help?

Ken Snell said:
In your SecondsToTime function, which I assume is returning a string, you
would use the Format function to ensure the leading zero.

Not knowing how the function produces its value, I can't give you any
specific suggestions. If you post the code, then we should be able to help
you.

--

Ken Snell
<MS ACCESS MVP>

kbrad said:
I have the following as a control source in the group footer section of a
report:
=SecondsToTime(Sum(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
This produces 7:11:04 or 22:44:21 as examples - how can I get a zero to
appear in front of 7:11:04 so it is hh:mm:ss format?

Alternatively, the following control source in the detail section
=TimeSerial(0,0,(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
gives the hh:mm:ss with a leading zero where necessary. Is it easier to
remove the zero here or put it in the group footer control source?

I don't mind which, Ijust want them the same.
Click to expand...
Click to expand...
 
It helps me understand what the function SecondsToTime is doing, but you
still need to post the code from that function so that we can help you
modify it accordingly.

I don't think it will work for you, as I'm assuming that this function is
returning a string, but you could try changing the "Format" property of that
textbox to
hh:mm:ss

But I am pretty sure that the Format function will be needed in the
SecondsToTime function to really make this work.
--

Ken Snell
<MS ACCESS MVP>


kbrad said:
This is in a report.
I am being sent an amount of time in hh:mm:ss which I am storing as
date/time format as that is how it gets to me. This is displayed in the
detail section of my report. I convert it to seconds using the
TimeToSeconds
function (this is hidden on the report) then use that to sum up in the
group
footer. I then use the SecondsToTime function to convert back to hh:ss:mm
for
display and don't get the leading zero where the total hours is <10.
Does this help?

Ken Snell said:
In your SecondsToTime function, which I assume is returning a string, you
would use the Format function to ensure the leading zero.

Not knowing how the function produces its value, I can't give you any
specific suggestions. If you post the code, then we should be able to
help
you.

--

Ken Snell
<MS ACCESS MVP>

kbrad said:
I have the following as a control source in the group footer section of
a
report:
=SecondsToTime(Sum(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
This produces 7:11:04 or 22:44:21 as examples - how can I get a zero to
appear in front of 7:11:04 so it is hh:mm:ss format?

Alternatively, the following control source in the detail section
=TimeSerial(0,0,(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
gives the hh:mm:ss with a leading zero where necessary. Is it easier to
remove the zero here or put it in the group footer control source?

I don't mind which, Ijust want them the same.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, brain dead - below is the function.

Function SecondsToTime(ByVal lngSeconds As Long) As String

Dim strHours As String
Dim lngHours As Long
Dim strMinutes As String
Dim lngMinutes As Long
Dim dblSeconds As Double
Dim strMinFmt As String
Dim strSecFmt As String

lngHours = lngSeconds \ 3600
lngSeconds = lngSeconds Mod 3600
lngMinutes = lngSeconds \ 60
lngSeconds = lngSeconds Mod 60

If lngHours > 0 Then
strHours = lngHours & ":"
strMinFmt = "00"
Else
strMinFmt = "0"
End If

If lngMinutes > 0 Then
strMinutes = Format(lngMinutes, strMinFmt) & ":"
strSecFmt = "00"
Else
strSecFmt = "0"
End If

SecondsToTime = _
strHours & strMinutes & Format(lngSeconds, strSecFmt)

End Function

Ken Snell said:
It helps me understand what the function SecondsToTime is doing, but you
still need to post the code from that function so that we can help you
modify it accordingly.

I don't think it will work for you, as I'm assuming that this function is
returning a string, but you could try changing the "Format" property of that
textbox to
hh:mm:ss

But I am pretty sure that the Format function will be needed in the
SecondsToTime function to really make this work.
--

Ken Snell
<MS ACCESS MVP>


kbrad said:
This is in a report.
I am being sent an amount of time in hh:mm:ss which I am storing as
date/time format as that is how it gets to me. This is displayed in the
detail section of my report. I convert it to seconds using the
TimeToSeconds
function (this is hidden on the report) then use that to sum up in the
group
footer. I then use the SecondsToTime function to convert back to hh:ss:mm
for
display and don't get the leading zero where the total hours is <10.
Does this help?

Ken Snell said:
In your SecondsToTime function, which I assume is returning a string, you
would use the Format function to ensure the leading zero.

Not knowing how the function produces its value, I can't give you any
specific suggestions. If you post the code, then we should be able to
help
you.

--

Ken Snell
<MS ACCESS MVP>

I have the following as a control source in the group footer section of
a
report:
=SecondsToTime(Sum(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
This produces 7:11:04 or 22:44:21 as examples - how can I get a zero to
appear in front of 7:11:04 so it is hh:mm:ss format?

Alternatively, the following control source in the detail section
=TimeSerial(0,0,(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
gives the hh:mm:ss with a leading zero where necessary. Is it easier to
remove the zero here or put it in the group footer control source?

I don't mind which, Ijust want them the same.
Click to expand...
Click to expand...
Click to expand...
 
Change this line:
strHours = lngHours & ":"

to this line:
strHours = Format(lngHours, "00") & ":"

--

Ken Snell
<MS ACCESS MVP>

kbrad said:
Sorry, brain dead - below is the function.

Function SecondsToTime(ByVal lngSeconds As Long) As String

Dim strHours As String
Dim lngHours As Long
Dim strMinutes As String
Dim lngMinutes As Long
Dim dblSeconds As Double
Dim strMinFmt As String
Dim strSecFmt As String

lngHours = lngSeconds \ 3600
lngSeconds = lngSeconds Mod 3600
lngMinutes = lngSeconds \ 60
lngSeconds = lngSeconds Mod 60

If lngHours > 0 Then
strHours = lngHours & ":"
strMinFmt = "00"
Else
strMinFmt = "0"
End If

If lngMinutes > 0 Then
strMinutes = Format(lngMinutes, strMinFmt) & ":"
strSecFmt = "00"
Else
strSecFmt = "0"
End If

SecondsToTime = _
strHours & strMinutes & Format(lngSeconds, strSecFmt)

End Function

Ken Snell said:
It helps me understand what the function SecondsToTime is doing, but you
still need to post the code from that function so that we can help you
modify it accordingly.

I don't think it will work for you, as I'm assuming that this function is
returning a string, but you could try changing the "Format" property of
that
textbox to
hh:mm:ss

But I am pretty sure that the Format function will be needed in the
SecondsToTime function to really make this work.
--

Ken Snell
<MS ACCESS MVP>


kbrad said:
This is in a report.
I am being sent an amount of time in hh:mm:ss which I am storing as
date/time format as that is how it gets to me. This is displayed in the
detail section of my report. I convert it to seconds using the
TimeToSeconds
function (this is hidden on the report) then use that to sum up in the
group
footer. I then use the SecondsToTime function to convert back to
hh:ss:mm
for
display and don't get the leading zero where the total hours is <10.
Does this help?

:

In your SecondsToTime function, which I assume is returning a string,
you
would use the Format function to ensure the leading zero.

Not knowing how the function produces its value, I can't give you any
specific suggestions. If you post the code, then we should be able to
help
you.

--

Ken Snell
<MS ACCESS MVP>

I have the following as a control source in the group footer section
of
a
report:
=SecondsToTime(Sum(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
This produces 7:11:04 or 22:44:21 as examples - how can I get a zero
to
appear in front of 7:11:04 so it is hh:mm:ss format?

Alternatively, the following control source in the detail section
=TimeSerial(0,0,(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
gives the hh:mm:ss with a leading zero where necessary. Is it easier
to
remove the zero here or put it in the group footer control source?

I don't mind which, Ijust want them the same.
Click to expand...
Click to expand...
Click to expand...
 
Many thanks for your help - that works fine now.

Ken Snell said:
Change this line:
strHours = lngHours & ":"

to this line:
strHours = Format(lngHours, "00") & ":"

--

Ken Snell
<MS ACCESS MVP>

kbrad said:
Sorry, brain dead - below is the function.

Function SecondsToTime(ByVal lngSeconds As Long) As String

Dim strHours As String
Dim lngHours As Long
Dim strMinutes As String
Dim lngMinutes As Long
Dim dblSeconds As Double
Dim strMinFmt As String
Dim strSecFmt As String

lngHours = lngSeconds \ 3600
lngSeconds = lngSeconds Mod 3600
lngMinutes = lngSeconds \ 60
lngSeconds = lngSeconds Mod 60

If lngHours > 0 Then
strHours = lngHours & ":"
strMinFmt = "00"
Else
strMinFmt = "0"
End If

If lngMinutes > 0 Then
strMinutes = Format(lngMinutes, strMinFmt) & ":"
strSecFmt = "00"
Else
strSecFmt = "0"
End If

SecondsToTime = _
strHours & strMinutes & Format(lngSeconds, strSecFmt)

End Function

Ken Snell said:
It helps me understand what the function SecondsToTime is doing, but you
still need to post the code from that function so that we can help you
modify it accordingly.

I don't think it will work for you, as I'm assuming that this function is
returning a string, but you could try changing the "Format" property of
that
textbox to
hh:mm:ss

But I am pretty sure that the Format function will be needed in the
SecondsToTime function to really make this work.
--

Ken Snell
<MS ACCESS MVP>


This is in a report.
I am being sent an amount of time in hh:mm:ss which I am storing as
date/time format as that is how it gets to me. This is displayed in the
detail section of my report. I convert it to seconds using the
TimeToSeconds
function (this is hidden on the report) then use that to sum up in the
group
footer. I then use the SecondsToTime function to convert back to
hh:ss:mm
for
display and don't get the leading zero where the total hours is <10.
Does this help?

:

In your SecondsToTime function, which I assume is returning a string,
you
would use the Format function to ensure the leading zero.

Not knowing how the function produces its value, I can't give you any
specific suggestions. If you post the code, then we should be able to
help
you.

--

Ken Snell
<MS ACCESS MVP>

I have the following as a control source in the group footer section
of
a
report:
=SecondsToTime(Sum(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
This produces 7:11:04 or 22:44:21 as examples - how can I get a zero
to
appear in front of 7:11:04 so it is hh:mm:ss format?

Alternatively, the following control source in the detail section
=TimeSerial(0,0,(DateDiff("s",[TimeFirstChart],[TimeLastChart])))
gives the hh:mm:ss with a leading zero where necessary. Is it easier
to
remove the zero here or put it in the group footer control source?

I don't mind which, Ijust want them the same.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top