Converting Time Fields

[Guest]

Hi,

I have a two text field that has time values. The values in these fields
have two digits for hours, a colon and then two digits for minutes. So 1
hour and 30 minutes would be 01:30. 45 minutes would be 00:45. I am trying
to add these values together. So for the first records, I want to add 1:30
and 00:45 together. This would be 02:15 or 2.25 hours. So I have two issues
to deal with. First, these are text fields which I probably have to convert
to numbers. Second, is that I need to convert the 00:00 format to a number
and then add the two fields together. Can anyone help?

Thanks,

 

[JohnFol]

format(cvdate(strField1) + cvdate(strField2), "hh:nn")

To demonstrate, this gives "01:45"
format(cvdate("01:00") + cvdate("0:45"), "hh:nn")

 

[Guest]

John,

Thanks for your help. I am getting an unusual error message. All of my
fields that are one hour are listed as 00:60 rather than 01:00. If it is
more than an hour, it is 01:15 or an hour and 15 minutes. Bu all values that
have a 00:60 get an error message when I try to add them. I am either trying
to add them to a value of 00:00 or 00:15. Any idea on why the 00:60 is
causing errors?

Thanks,

 

[John Vinson]

John,

Thanks for your help. I am getting an unusual error message. All of my
fields that are one hour are listed as 00:60 rather than 01:00. If it is
more than an hour, it is 01:15 or an hour and 15 minutes. Bu all values that
have a 00:60 get an error message when I try to add them. I am either trying
to add them to a value of 00:00 or 00:15. Any idea on why the 00:60 is
causing errors?

Thanks,

Have you ever seen :60 on a digital clock? No, and you never will. One
second after 00:59:59 is 01:00:00, and NOT 00:60:00. 60 and any
larger number is an error in a seconds or minutes portion of a
date/time value.

Any chance you can edit your source data so that 00:60 is replaced by
a valid 01:00?

John W. Vinson[MVP]

 

[Guest]

John,

I can run an update query that will change all of the 00:60 to 01:00. This
seems to have solved the problem. However, I can't seem to sum all of my
records. I have a field called TotalTime that has about 300 records all with
the 00:00 format. This is a time clock table that give hours that employees
work. There is a field called EmployeeID. What I want to do is the group by
EmployeeId and thensum the TotalTime field to get total hours worked by
employee. I think because it is a text field it won't let me do this.

Thanks,

 

[John Vinson]

John,

I can run an update query that will change all of the 00:60 to 01:00. This
seems to have solved the problem. However, I can't seem to sum all of my
records. I have a field called TotalTime that has about 300 records all with
the 00:00 format. This is a time clock table that give hours that employees
work. There is a field called EmployeeID. What I want to do is the group by
EmployeeId and thensum the TotalTime field to get total hours worked by
employee. I think because it is a text field it won't let me do this.

<shrug>

Of course. Text is text. As far as Access is concerned, "01:00" is a
set of five alphanumeric characters, no different than "XY#5!". It's
not a number, and it's not a time - it's text, and text is not
summable. Access doesn't know what meaning you assign to this text
string!

I'd suggest using an expression like

1440. * CDbl(CDate([TotalTime]))

CDate will change the text string to a Date/Time value (stored as a
number, days and fractions of a day); CDbl() will return a Double
Float number equal to the date/time value (e.g. 06:00 will be returned
as 0.25); 1440 will convert this to minutes. You can then sum the
minutes.

John W. Vinson[MVP]