converts secs to nn:ss

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

Guest

Hi,

I have a field ('Time') in short time format, and another that is number of
seconds (i.e 9 or 148 etc, 'Seconds'), in number format. I would like to add
the seconds to the 'Time' field. I believe I have to first convert the
Seconds field into nn:ss, however I keep getting a type conversion failure
error message when I try to run an update query on my new field 'SecstoMins'
using the following formula:

[Seconds] \ 60 & Format([Seconds] Mod 60, "\:00")

Is the problem that the Seconds field is the wrong type? Or is it that the
'SecstoMins' field is perhaps the wrong type (short time format)? I've tried
switching the formats around with no luck. Any help is much appreciated.

Thanks in advance,
Theresa
 
You could use DateAdd("s",Seconds,[Time]) to get a new time. This gives you
time as a point in time on a clock, it doesn't give you time as in the duration
of an event. In other words, when you add enough seconds to go beyond midnight,
your time will be on another day.
 
Hi,

I have a field ('Time') in short time format, and another that is number of
seconds (i.e 9 or 148 etc, 'Seconds'), in number format. I would like to add
the seconds to the 'Time' field. I believe I have to first convert the
Seconds field into nn:ss, however I keep getting a type conversion failure
error message when I try to run an update query on my new field 'SecstoMins'
using the following formula:

[Seconds] \ 60 & Format([Seconds] Mod 60, "\:00")

Is the problem that the Seconds field is the wrong type? Or is it that the
'SecstoMins' field is perhaps the wrong type (short time format)? I've tried
switching the formats around with no luck. Any help is much appreciated.
Click to expand...

The Format function returns a text string - and you can't do
arithmatic with text strings. Your assumption that you need to convert
the Seconds field is incorrect as well.

To add 148 seconds to a date/time field, use the DateAdd function:

NewTime: DateAdd("s", [Seconds], [Time])

Note that Time is a bad choice of fieldname - it's a reserved word for
the built-in system clock Time() function.

John W. Vinson[MVP]
 
Thanks! However, when I use the new formula "DateAdd("s", [Seconds],
[Time])" I still get a 'field type conversion failure'...I opted to ignore
the error and run the query anyway, and it worked no prob...not sure what the
error's all about, but oh well!

Thanks again,
Theresa

P.S. My field headings are different than above - I just simplified them for
clarity in this posting. But tip noted :)
 
Thanks! However, when I use the new formula "DateAdd("s", [Seconds],
[Time])" I still get a 'field type conversion failure'...I opted to ignore
the error and run the query anyway, and it worked no prob...not sure what the
error's all about, but oh well!
Click to expand...

Well, you will only get an error message if you're making an error,
and I'd really recommend tracking it down and fixing it! It might have
nothing to do with this particular field, though. Could you post the
SQL view of the query and indicate the datatype of each field
involved?

John W. Vinson[MVP]
 
Back
Top