Add Times

[Mike]

Could anyone help me build a query statement take converts the following into
hh:mm:ss

TTL Time Connected Hrs
02h 03m
00h 18m
00h 23m
00h 15m
02h 16m
02h 09m
00h 42m
1d 01h 55m
00h 59m

Notice some time have a day value and some do not. If it has a day value, it
will have only one digit before the d. I have the following excel formula
that works, but I need to have Access convert it.

=IF(ISERROR(FIND("d",D2)),0,LEFT(D2,FIND("d",D2)-1)*24)+MID(D2,FIND("h",D2)-2,2)+MID(D2,FIND("m",D2)-2,2)/60

I also have an Access statement that I thought would work, but I don't have
a place for the day field:

Expr1:
TimeSerial(Mid([FieldName],InStr(1,[FieldName],"h")-2,2),Mid([FieldName],InStr(1,[FieldName],"m")-2,2),0)

I just need to add the day statement in the above and 0 if there is no d.

Thanks for your help!
Mike Chamis

 

[Michel Walsh]

Hi,





eval( Replace(Replace( Replace( myField, "d", "*1440+") , "h", "*60+"), "m",
""))


.... or something like that.

The idea is to replace the dhm-base by its equivalent decimal weights and to
make the sum of the expression. That also assume there is always a "m"
value, even if it is 0, else, the string, from 1d 3h would result into
"1*1440+ 3*60+" which is NOT evaluable (error of syntax, the final +
having no right argument).



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


ah...

once you have the result in number of minutes, you have to divide the result
by 1440, and convert the thing into a date:


CDate( eval( ... ) / 1440 )



Since Access has not "Interval" of time, format, there will be a problem
when there is more than 24h (even if you use a 24h format, instead of a 12h
format with AM/PM suffix). Depends of what you want to do with that
"interval" value, but it is OK if Access displays what you think has 30
hours as


? CDate( 1.25)
1899.12.31 06:00:00



(format may vary, mine is with the double dot.... observe that 1899-12-31
MAY indicate two subtractions, 1899/12/31 MAY indicates two divisions, but
the double dot CANNOT indicate a decimal number, since decimal number have
only ONE dot... so the double dot is without any possible confusion... once
we know about it, and people who use the double dot use yyyy then mm then
dd, so the double dot remove two ambiguities: one about possible arithmetic,
one about the ordering of the mm and dd (and yy)).



Hoping it may help,
Vanderghast, Access MVP

 

[Mike]

Michel,

That was a great Idea. I converted the output to Hours and got the needed
information.

Thanks,
Mike

Hi,


ah...

once you have the result in number of minutes, you have to divide the result
by 1440, and convert the thing into a date:


CDate( eval( ... ) / 1440 )



Since Access has not "Interval" of time, format, there will be a problem
when there is more than 24h (even if you use a 24h format, instead of a 12h
format with AM/PM suffix). Depends of what you want to do with that
"interval" value, but it is OK if Access displays what you think has 30
hours as


? CDate( 1.25)
1899.12.31 06:00:00



(format may vary, mine is with the double dot.... observe that 1899-12-31
MAY indicate two subtractions, 1899/12/31 MAY indicates two divisions, but
the double dot CANNOT indicate a decimal number, since decimal number have
only ONE dot... so the double dot is without any possible confusion... once
we know about it, and people who use the double dot use yyyy then mm then
dd, so the double dot remove two ambiguities: one about possible arithmetic,
one about the ordering of the mm and dd (and yy)).



Hoping it may help,
Vanderghast, Access MVP

Could anyone help me build a query statement take converts the following
into
hh:mm:ss

TTL Time Connected Hrs
02h 03m
00h 18m
00h 23m
00h 15m
02h 16m
02h 09m
00h 42m
1d 01h 55m
00h 59m

Notice some time have a day value and some do not. If it has a day value,
it
will have only one digit before the d. I have the following excel formula
that works, but I need to have Access convert it.

=IF(ISERROR(FIND("d",D2)),0,LEFT(D2,FIND("d",D2)-1)*24)+MID(D2,FIND("h",D2)-2,2)+MID(D2,FIND("m",D2)-2,2)/60

I also have an Access statement that I thought would work, but I don't
have
a place for the day field:

Expr1:
TimeSerial(Mid([FieldName],InStr(1,[FieldName],"h")-2,2),Mid([FieldName],InStr(1,[FieldName],"m")-2,2),0)

I just need to add the day statement in the above and 0 if there is no d.

Thanks for your help!
Mike Chamis