need a format for value

[short_sweet]

I have a what I hope is an easy problem to fix:

Text box 1: named Start_Time formatted as a Number
Text box 2: named End_Time formatted as a Number
Text box 3: named Hours formatted as a Number

Formula for calculating the time difference works great, but in Text box 3 I
need to get the value to show as decimal (or whatever it takes)

Example:

User will enter time as 24hr format (800 to 1530)

Start time is 800
End time is 1530
I need to get the Hours text box to show 7.5 hours. Right now it shows 730.0

Thank you,

 

[Douglas J Steele]

It's showing 730 because it doesn't know that 800 and 1530 are times.

Try using the following for your Hours textbox:

DateDiff("n", Format(Me.Start_Time, "00:00"), Format(Me.End_Time,
"00:00"))/60

 

[Klatuu]

datediff("n",format(txtstart,"00:00"),format(txtend,"00:00"))/60

 

[Klatuu]

Damn, Douglas, that is clever!

I guess now, it is time for a beer

 

[short_sweet]

Thank you but where do I put the formula? In the event procedure that I
have here:

Private Sub Hours_Exit(Cancel As Integer)
Dim intHours As Integer
intHours = [End_Time] - [Start_Time]
Me![Hours] = intHours

End Sub

It's showing 730 because it doesn't know that 800 and 1530 are times.

Try using the following for your Hours textbox:

DateDiff("n", Format(Me.Start_Time, "00:00"), Format(Me.End_Time,

 

[Klatuu]

That would not do it until you leave the Hours text box. To make it work
correctly, you will need it in three places - once in the Current event of
the form, in the After Update event of StartTime, and the After Update event
of EndTime.

For the Current event:
(You need it here, because I just know you are not doing a no no by storing
the calculated value) This will display the hours for existing records, but
bypass new records.

If Not Me.NewRecord Then
Me.Hours = DateDiff("n", Format(Me.Start_Time, "00:00"), _
Format(Me.End_Time, "00:00"))/60
End If

You need it in both contorls and you need to be sure both have values before
you do the calculation. This will avoid creating errors when users put
things in out of order, but then, they never do that, do they?
For the other two events:

If Not IsNull(Me.Start_Time) And Not IsNull(Me.End_Time) Then
Me.Hours = DateDiff("n", Format(Me.Start_Time, "00:00"), _
Format(Me.End_Time, "00:00"))/60
End If

Thank you but where do I put the formula? In the event procedure that I
have here:

Private Sub Hours_Exit(Cancel As Integer)
Dim intHours As Integer
intHours = [End_Time] - [Start_Time]
Me![Hours] = intHours

End Sub

It's showing 730 because it doesn't know that 800 and 1530 are times.

Try using the following for your Hours textbox:

DateDiff("n", Format(Me.Start_Time, "00:00"), Format(Me.End_Time,

"00:00"))/60

 

[short_sweet]

Thank you, it works great to give me the whole number but not the 7.5 for the
number of hours.
say someone enters 700 for the start time and 1430 for end time that should
show 6.5 in the hours text box.

That would not do it until you leave the Hours text box. To make it work
correctly, you will need it in three places - once in the Current event of
the form, in the After Update event of StartTime, and the After Update event
of EndTime.

For the Current event:
(You need it here, because I just know you are not doing a no no by storing
the calculated value) This will display the hours for existing records, but
bypass new records.

If Not Me.NewRecord Then
Me.Hours = DateDiff("n", Format(Me.Start_Time, "00:00"), _
Format(Me.End_Time, "00:00"))/60
End If

You need it in both contorls and you need to be sure both have values before
you do the calculation. This will avoid creating errors when users put
things in out of order, but then, they never do that, do they?
For the other two events:

If Not IsNull(Me.Start_Time) And Not IsNull(Me.End_Time) Then
Me.Hours = DateDiff("n", Format(Me.Start_Time, "00:00"), _
Format(Me.End_Time, "00:00"))/60
End If

Thank you but where do I put the formula? In the event procedure that I
have here:

Private Sub Hours_Exit(Cancel As Integer)
Dim intHours As Integer
intHours = [End_Time] - [Start_Time]
Me![Hours] = intHours

End Sub

It's showing 730 because it doesn't know that 800 and 1530 are times.

Try using the following for your Hours textbox:

DateDiff("n", Format(Me.Start_Time, "00:00"), Format(Me.End_Time,

"00:00"))/60

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a what I hope is an easy problem to fix:

Text box 1: named Start_Time formatted as a Number
Text box 2: named End_Time formatted as a Number
Text box 3: named Hours formatted as a Number

Formula for calculating the time difference works great, but in Text box 3
I
need to get the value to show as decimal (or whatever it takes)

Example:

User will enter time as 24hr format (800 to 1530)

Start time is 800
End time is 1530
I need to get the Hours text box to show 7.5 hours. Right now it shows
730.0

Thank you,

 

[Klatuu]

Readjust your calculator, 07:00 to 14:30 is 7 hour 30 minutes or 7.5 hours.

If you are not seeing the decimal, check the formatting of your Hours control.
Do the calculation in the immediate window and you will see that the
calculation is correct.

You said in an earlier post your start and end times are formatted as
numbers. Did you actually set the format property for the controls? If you
did, remove it.

Thank you, it works great to give me the whole number but not the 7.5 for the
number of hours.
say someone enters 700 for the start time and 1430 for end time that should
show 6.5 in the hours text box.

That would not do it until you leave the Hours text box. To make it work
correctly, you will need it in three places - once in the Current event of
the form, in the After Update event of StartTime, and the After Update event
of EndTime.

For the Current event:
(You need it here, because I just know you are not doing a no no by storing
the calculated value) This will display the hours for existing records, but
bypass new records.

If Not Me.NewRecord Then
Me.Hours = DateDiff("n", Format(Me.Start_Time, "00:00"), _
Format(Me.End_Time, "00:00"))/60
End If

You need it in both contorls and you need to be sure both have values before
you do the calculation. This will avoid creating errors when users put
things in out of order, but then, they never do that, do they?
For the other two events:

If Not IsNull(Me.Start_Time) And Not IsNull(Me.End_Time) Then
Me.Hours = DateDiff("n", Format(Me.Start_Time, "00:00"), _
Format(Me.End_Time, "00:00"))/60
End If

Thank you but where do I put the formula? In the event procedure that I
have here:

Private Sub Hours_Exit(Cancel As Integer)
Dim intHours As Integer
intHours = [End_Time] - [Start_Time]
Me![Hours] = intHours

End Sub


:

It's showing 730 because it doesn't know that 800 and 1530 are times.

Try using the following for your Hours textbox:

DateDiff("n", Format(Me.Start_Time, "00:00"), Format(Me.End_Time,
"00:00"))/60

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a what I hope is an easy problem to fix:

Text box 1: named Start_Time formatted as a Number
Text box 2: named End_Time formatted as a Number
Text box 3: named Hours formatted as a Number

Formula for calculating the time difference works great, but in Text box 3
I
need to get the value to show as decimal (or whatever it takes)

Example:

User will enter time as 24hr format (800 to 1530)

Start time is 800
End time is 1530
I need to get the Hours text box to show 7.5 hours. Right now it shows
730.0

Thank you,

 

[short_sweet]

yes, sorry, I can calculate, I just hit the wrong number when typing it in
here.

removed all the formatting in the controls and still it rounds up the number.

Readjust your calculator, 07:00 to 14:30 is 7 hour 30 minutes or 7.5 hours.

If you are not seeing the decimal, check the formatting of your Hours control.
Do the calculation in the immediate window and you will see that the
calculation is correct.

You said in an earlier post your start and end times are formatted as
numbers. Did you actually set the format property for the controls? If you
did, remove it.

Thank you, it works great to give me the whole number but not the 7.5 for the
number of hours.
say someone enters 700 for the start time and 1430 for end time that should
show 6.5 in the hours text box.

That would not do it until you leave the Hours text box. To make it work
correctly, you will need it in three places - once in the Current event of
the form, in the After Update event of StartTime, and the After Update event
of EndTime.

For the Current event:
(You need it here, because I just know you are not doing a no no by storing
the calculated value) This will display the hours for existing records, but
bypass new records.

If Not Me.NewRecord Then
Me.Hours = DateDiff("n", Format(Me.Start_Time, "00:00"), _
Format(Me.End_Time, "00:00"))/60
End If

You need it in both contorls and you need to be sure both have values before
you do the calculation. This will avoid creating errors when users put
things in out of order, but then, they never do that, do they?
For the other two events:

If Not IsNull(Me.Start_Time) And Not IsNull(Me.End_Time) Then
Me.Hours = DateDiff("n", Format(Me.Start_Time, "00:00"), _
Format(Me.End_Time, "00:00"))/60
End If


:

Thank you but where do I put the formula? In the event procedure that I
have here:

Private Sub Hours_Exit(Cancel As Integer)
Dim intHours As Integer
intHours = [End_Time] - [Start_Time]
Me![Hours] = intHours

End Sub


:

It's showing 730 because it doesn't know that 800 and 1530 are times.

Try using the following for your Hours textbox:

DateDiff("n", Format(Me.Start_Time, "00:00"), Format(Me.End_Time,
"00:00"))/60

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a what I hope is an easy problem to fix:

Text box 1: named Start_Time formatted as a Number
Text box 2: named End_Time formatted as a Number
Text box 3: named Hours formatted as a Number

Formula for calculating the time difference works great, but in Text box 3
I
need to get the value to show as decimal (or whatever it takes)

Example:

User will enter time as 24hr format (800 to 1530)

Start time is 800
End time is 1530
I need to get the Hours text box to show 7.5 hours. Right now it shows
730.0

Thank you,

 

[short_sweet]

Klatuu,

Thank you very much for your help and patience. I have figured out how to
fix it.

Readjust your calculator, 07:00 to 14:30 is 7 hour 30 minutes or 7.5 hours.

If you are not seeing the decimal, check the formatting of your Hours control.
Do the calculation in the immediate window and you will see that the
calculation is correct.

You said in an earlier post your start and end times are formatted as
numbers. Did you actually set the format property for the controls? If you
did, remove it.

Thank you, it works great to give me the whole number but not the 7.5 for the
number of hours.
say someone enters 700 for the start time and 1430 for end time that should
show 6.5 in the hours text box.

That would not do it until you leave the Hours text box. To make it work
correctly, you will need it in three places - once in the Current event of
the form, in the After Update event of StartTime, and the After Update event
of EndTime.

For the Current event:
(You need it here, because I just know you are not doing a no no by storing
the calculated value) This will display the hours for existing records, but
bypass new records.

If Not Me.NewRecord Then
Me.Hours = DateDiff("n", Format(Me.Start_Time, "00:00"), _
Format(Me.End_Time, "00:00"))/60
End If

You need it in both contorls and you need to be sure both have values before
you do the calculation. This will avoid creating errors when users put
things in out of order, but then, they never do that, do they?
For the other two events:

If Not IsNull(Me.Start_Time) And Not IsNull(Me.End_Time) Then
Me.Hours = DateDiff("n", Format(Me.Start_Time, "00:00"), _
Format(Me.End_Time, "00:00"))/60
End If


:

Thank you but where do I put the formula? In the event procedure that I
have here:

Private Sub Hours_Exit(Cancel As Integer)
Dim intHours As Integer
intHours = [End_Time] - [Start_Time]
Me![Hours] = intHours

End Sub


:

It's showing 730 because it doesn't know that 800 and 1530 are times.

Try using the following for your Hours textbox:

DateDiff("n", Format(Me.Start_Time, "00:00"), Format(Me.End_Time,
"00:00"))/60

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a what I hope is an easy problem to fix:

Text box 1: named Start_Time formatted as a Number
Text box 2: named End_Time formatted as a Number
Text box 3: named Hours formatted as a Number

Formula for calculating the time difference works great, but in Text box 3
I
need to get the value to show as decimal (or whatever it takes)

Example:

User will enter time as 24hr format (800 to 1530)

Start time is 800
End time is 1530
I need to get the Hours text box to show 7.5 hours. Right now it shows
730.0

Thank you,