By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,284 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Time Duration Problems

P: n/a
CDF
In one of my tables, I have a field that has time durations in it.
The field is currently in text format. Is there a way that I can
convert this field into a format where I can SUM the durations? Help
is appreciated!

Thanks,

Corey
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Text would be the least useful/reliable field type for this data.

Consider replacing it with a Number (Long Integer) to store the number of
whole minutes (or seconds). It's easy to sum the values:
DSum("Minutes", "MyTable")
or display as hours:minutes:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

For data entry, you can provide 2 text boxes where the user enters the hours
and minutes, and use the AfterUpdate event of these text boxes to write the
number of minutes to the hidden field:
Me.Minutes = Nz(60 * Me.txtHours,0) + Nz(Me.txtMinutes, 0)

Some people try using the Date/Time field for storing durations. That's
feasible, but for summing values greater than 24 hours you have to convert
to minutes and use the display method shown above. To convert to minutes:
DateDiff("n", #0:00#, [MyField])

If you wish to continue with the Text type, and are sure there are no
invalid entries, you could combine these various data casting methods. A
calculated field of minutes to sum would be:
DateDiff("n", #0:00#, CDate([MyField]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"CDF" <cf******@sprintspectrum.com> wrote in message
news:5c**************************@posting.google.c om...
In one of my tables, I have a field that has time durations in it.
The field is currently in text format. Is there a way that I can
convert this field into a format where I can SUM the durations? Help
is appreciated!

Thanks,

Corey

Nov 12 '05 #2

P: n/a
cf******@sprintspectrum.com (CDF) wrote in message news:<5c**************************@posting.google. com>...
In one of my tables, I have a field that has time durations in it.
The field is currently in text format. Is there a way that I can
convert this field into a format where I can SUM the durations? Help
is appreciated!

Thanks,

Corey


you should be able to use CDate to convert the thing to a time... and
then go from there... either that or parse it and turn it into
minutes...
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.