String to Time

[williamr]

I have some information in Excel that expresses the length of a phone call.
It's a text field and when I import it the option for type of field is grayed
out. I would like to run an update query and put that information into
another field and make it a number. Any help would be appreciated.

Thanks

 

[williamr]

After further review - it's a general field and it look like 09:00

 

[Marshall Barton]

I have some information in Excel that expresses the length of a phone call.
It's a text field and when I import it the option for type of field is grayed
out. I would like to run an update query and put that information into
another field and make it a number.

What does the text value look like?

What kind of number do you want?

 

[Marshall Barton]

Sorry, but "general field" is meaningless. There is a
format called General Number, but it does not apply to text
values.

Regardless of that, I really need to know what kind of
number result you want out of this. Do you want the total
number of seconds or what?

If you want it to be a Date/Time data type, then you can
convert it from a text string by using the CDate function.
The Date/Time value can be treated as if it were a number of
type Double, but it would only be the fraction of a day,
which is probably not what you want.

All I know at this point is that you have a text string
"09:00" and you want it to be some kind of number. I can't
tell you how to do something if I don't know what that
simething is.

 

[williamr]

Marshall, Hi. Here is what I have, I have 2 fields, "Time" which is a text
field. It has text that looks like 10:40, then I have a field named
Duration, it is a text field and has data in it like 0:01:16. What I want to
do is convert these text fields to a time field and keep the data in it so I
can add time to both fields.

Thanks
Wm.

Sorry, but "general field" is meaningless. There is a
format called General Number, but it does not apply to text
values.

Regardless of that, I really need to know what kind of
number result you want out of this. Do you want the total
number of seconds or what?

If you want it to be a Date/Time data type, then you can
convert it from a text string by using the CDate function.
The Date/Time value can be treated as if it were a number of
type Double, but it would only be the fraction of a day,
which is probably not what you want.

All I know at this point is that you have a text string
"09:00" and you want it to be some kind of number. I can't
tell you how to do something if I don't know what that
simething is.
--
Marsh
MVP [MS Access]

After further review - it's a general field and it look like 09:00

 

[Marshall Barton]

Try creating a query that retrieves the relevant information
from the table. Add two calculated fields to the query

StartTime: CDate([Time])
Elapsed: CDate(Duration)

so you can check to see if you get the desired results.
Next, add more calculated fields and add your other "time"
to those values. For example:

StopTime: StartTime + Elaspsed

If that works correctly, then you can add two Date/Time
fields to your table (using different names). Then you can
use an update query to save the converted values:

UPDATE table
SET newstarttimefield = CDate([Time]),
newdurationfield = CDate(Duration)

===========================
Note that using a date/time field to hold a duration value
is not really a good idea. As I implied earlier, a duration
is really 76 seconds, not 1 minute and 16 seconds after
midnight.

Also, if you do not have a date part to your start amd end
time values, you will run into serious difficulties
calculating the duration across a midnight boundary.

If you have the start and stop time values, you can
calculate the Elapsed time in seconds using:
DateDiff("s", StartTime, StopTime)
Vice versa, you can calculate the StopTime by adding the
Elapsed time in seconds to the StartIme:
DateAdd("s", Elapsed, StartTime)

This is not a trivial subject and I recommend that you spend
a few minutes getting your head wrapped around the issues.

 

[williamr]

I added a field named duration1, a date/time field, ran an update query that
takes the text field "Duration (0:01:52) and turnes it into time - but cuts
off the seconds. Leaves me with (0:01). I formated the the field as short
time. Any suggestions?

Thanks
Wm.

Try creating a query that retrieves the relevant information
from the table. Add two calculated fields to the query

StartTime: CDate([Time])
Elapsed: CDate(Duration)

so you can check to see if you get the desired results.
Next, add more calculated fields and add your other "time"
to those values. For example:

StopTime: StartTime + Elaspsed

If that works correctly, then you can add two Date/Time
fields to your table (using different names). Then you can
use an update query to save the converted values:

UPDATE table
SET newstarttimefield = CDate([Time]),
newdurationfield = CDate(Duration)

===========================
Note that using a date/time field to hold a duration value
is not really a good idea. As I implied earlier, a duration
is really 76 seconds, not 1 minute and 16 seconds after
midnight.

Also, if you do not have a date part to your start amd end
time values, you will run into serious difficulties
calculating the duration across a midnight boundary.

If you have the start and stop time values, you can
calculate the Elapsed time in seconds using:
DateDiff("s", StartTime, StopTime)
Vice versa, you can calculate the StopTime by adding the
Elapsed time in seconds to the StartIme:
DateAdd("s", Elapsed, StartTime)

This is not a trivial subject and I recommend that you spend
a few minutes getting your head wrapped around the issues.
--
Marsh
MVP [MS Access]

Marshall, Hi. Here is what I have, I have 2 fields, "Time" which is a text
field. It has text that looks like 10:40, then I have a field named
Duration, it is a text field and has data in it like 0:01:16. What I want to
do is convert these text fields to a time field and keep the data in it so I
can add time to both fields.

 

[Marshall Barton]

Another reason why I don't like those named formats.

Change the format to h:nn:ss
--
Marsh
MVP [MS Access]

I added a field named duration1, a date/time field, ran an update query that
takes the text field "Duration (0:01:52) and turnes it into time - but cuts
off the seconds. Leaves me with (0:01). I formated the the field as short
time. Any suggestions?

Try creating a query that retrieves the relevant information
from the table. Add two calculated fields to the query

StartTime: CDate([Time])
Elapsed: CDate(Duration)

so you can check to see if you get the desired results.
Next, add more calculated fields and add your other "time"
to those values. For example:

StopTime: StartTime + Elaspsed

If that works correctly, then you can add two Date/Time
fields to your table (using different names). Then you can
use an update query to save the converted values:

UPDATE table
SET newstarttimefield = CDate([Time]),
newdurationfield = CDate(Duration)

===========================
Note that using a date/time field to hold a duration value
is not really a good idea. As I implied earlier, a duration
is really 76 seconds, not 1 minute and 16 seconds after
midnight.

Also, if you do not have a date part to your start amd end
time values, you will run into serious difficulties
calculating the duration across a midnight boundary.

If you have the start and stop time values, you can
calculate the Elapsed time in seconds using:
DateDiff("s", StartTime, StopTime)
Vice versa, you can calculate the StopTime by adding the
Elapsed time in seconds to the StartIme:
DateAdd("s", Elapsed, StartTime)

This is not a trivial subject and I recommend that you spend
a few minutes getting your head wrapped around the issues.

Marshall, Hi. Here is what I have, I have 2 fields, "Time" which is a text
field. It has text that looks like 10:40, then I have a field named
Duration, it is a text field and has data in it like 0:01:16. What I want to
do is convert these text fields to a time field and keep the data in it so I
can add time to both fields.

:
Sorry, but "general field" is meaningless. There is a
format called General Number, but it does not apply to text
values.

Regardless of that, I really need to know what kind of
number result you want out of this. Do you want the total
number of seconds or what?

If you want it to be a Date/Time data type, then you can
convert it from a text string by using the CDate function.
The Date/Time value can be treated as if it were a number of
type Double, but it would only be the fraction of a day,
which is probably not what you want.

All I know at this point is that you have a text string
"09:00" and you want it to be some kind of number. I can't
tell you how to do something if I don't know what that
simething is.


williamr wrote:
After further review - it's a general field and it look like 09:00


williamr wrote:
I have some information in Excel that expresses the length of a phone call.
It's a text field and when I import it the option for type of field is grayed
out. I would like to run an update query and put that information into
another field and make it a number.


:
What does the text value look like?

What kind of number do you want?

 

[open a adobe file from a command button]

Marshall, Hi. I've tried all the formats in the menu and nothing works. The
"Duration" field looks like this after being imported from Excel, 0:01:52.
I created a new field name "Duration1", trying to turn the text from the
Field "Duration" into a time. The problem is it puts in the time as 12:01:52
AM. All of the durations start with "12" I then used "Duration1" with the
format h:nn:ss, nn:ss, with both long and short time, I get the same
results, cutting out the seconds. What do you think? Don't quit my day job!!

Thanks
Wm.

Another reason why I don't like those named formats.

Change the format to h:nn:ss
--
Marsh
MVP [MS Access]

I added a field named duration1, a date/time field, ran an update query that
takes the text field "Duration (0:01:52) and turnes it into time - but cuts
off the seconds. Leaves me with (0:01). I formated the the field as short
time. Any suggestions?

Try creating a query that retrieves the relevant information
from the table. Add two calculated fields to the query

StartTime: CDate([Time])
Elapsed: CDate(Duration)

so you can check to see if you get the desired results.
Next, add more calculated fields and add your other "time"
to those values. For example:

StopTime: StartTime + Elaspsed

If that works correctly, then you can add two Date/Time
fields to your table (using different names). Then you can
use an update query to save the converted values:

UPDATE table
SET newstarttimefield = CDate([Time]),
newdurationfield = CDate(Duration)

===========================
Note that using a date/time field to hold a duration value
is not really a good idea. As I implied earlier, a duration
is really 76 seconds, not 1 minute and 16 seconds after
midnight.

Also, if you do not have a date part to your start amd end
time values, you will run into serious difficulties
calculating the duration across a midnight boundary.

If you have the start and stop time values, you can
calculate the Elapsed time in seconds using:
DateDiff("s", StartTime, StopTime)
Vice versa, you can calculate the StopTime by adding the
Elapsed time in seconds to the StartIme:
DateAdd("s", Elapsed, StartTime)

This is not a trivial subject and I recommend that you spend
a few minutes getting your head wrapped around the issues.


williamr wrote:

Marshall, Hi. Here is what I have, I have 2 fields, "Time" which is a text
field. It has text that looks like 10:40, then I have a field named
Duration, it is a text field and has data in it like 0:01:16. What I want to
do is convert these text fields to a time field and keep the data in it so I
can add time to both fields.

:
Sorry, but "general field" is meaningless. There is a
format called General Number, but it does not apply to text
values.

Regardless of that, I really need to know what kind of
number result you want out of this. Do you want the total
number of seconds or what?

If you want it to be a Date/Time data type, then you can
convert it from a text string by using the CDate function.
The Date/Time value can be treated as if it were a number of
type Double, but it would only be the fraction of a day,
which is probably not what you want.

All I know at this point is that you have a text string
"09:00" and you want it to be some kind of number. I can't
tell you how to do something if I don't know what that
simething is.


williamr wrote:
After further review - it's a general field and it look like 09:00


williamr wrote:
I have some information in Excel that expresses the length of a phone call.
It's a text field and when I import it the option for type of field is grayed
out. I would like to run an update query and put that information into
another field and make it a number.


:
What does the text value look like?

What kind of number do you want?

 

[John W. Vinson]

Marshall, Hi. I've tried all the formats in the menu and nothing works. The
"Duration" field looks like this after being imported from Excel, 0:01:52.
I created a new field name "Duration1", trying to turn the text from the
Field "Duration" into a time. The problem is it puts in the time as 12:01:52
AM. All of the durations start with "12" I then used "Duration1" with the
format h:nn:ss, nn:ss, with both long and short time, I get the same
results, cutting out the seconds. What do you think? Don't quit my day job!!

Access Date/Time fields are NOT really suitable for durations. This is one of
many reasons. Another is that durations exceeding 24 hours are not displayable
- 25 hours is actually #12/31/1899 01:00:00#.

I'd store your duration of 1 minute 52 seconds as a Long Integer or Double (if
you want fractional seconds) 112. You can *display* it in nn:ss appearance
using an expression

[duration] \ 60 & Format([duration] MOD 60, ":00")

John W. Vinson [MVP]

 

[Marshall Barton]

Don't select a format from the list. Just type
h:nn:ss
in the Format property.

Even after you get that to work, there will be other issues
associated with using a date/time field to hold a duration.
Please take some time to reread my earlier warning along
with John's expansion on the correct approach.
--
Marsh
MVP [MS Access]

Marshall, Hi. I've tried all the formats in the menu and nothing works. The
"Duration" field looks like this after being imported from Excel, 0:01:52.
I created a new field name "Duration1", trying to turn the text from the
Field "Duration" into a time. The problem is it puts in the time as 12:01:52
AM. All of the durations start with "12" I then used "Duration1" with the
format h:nn:ss, nn:ss, with both long and short time, I get the same
results, cutting out the seconds. What do you think? Don't quit my day job!!

Another reason why I don't like those named formats.

Change the format to h:nn:ss

I added a field named duration1, a date/time field, ran an update query that
takes the text field "Duration (0:01:52) and turnes it into time - but cuts
off the seconds. Leaves me with (0:01). I formated the the field as short
time. Any suggestions?


:

Try creating a query that retrieves the relevant information
from the table. Add two calculated fields to the query

StartTime: CDate([Time])
Elapsed: CDate(Duration)

so you can check to see if you get the desired results.
Next, add more calculated fields and add your other "time"
to those values. For example:

StopTime: StartTime + Elaspsed

If that works correctly, then you can add two Date/Time
fields to your table (using different names). Then you can
use an update query to save the converted values:

UPDATE table
SET newstarttimefield = CDate([Time]),
newdurationfield = CDate(Duration)

===========================
Note that using a date/time field to hold a duration value
is not really a good idea. As I implied earlier, a duration
is really 76 seconds, not 1 minute and 16 seconds after
midnight.

Also, if you do not have a date part to your start amd end
time values, you will run into serious difficulties
calculating the duration across a midnight boundary.

If you have the start and stop time values, you can
calculate the Elapsed time in seconds using:
DateDiff("s", StartTime, StopTime)
Vice versa, you can calculate the StopTime by adding the
Elapsed time in seconds to the StartIme:
DateAdd("s", Elapsed, StartTime)

This is not a trivial subject and I recommend that you spend
a few minutes getting your head wrapped around the issues.


williamr wrote:

Marshall, Hi. Here is what I have, I have 2 fields, "Time" which is a text
field. It has text that looks like 10:40, then I have a field named
Duration, it is a text field and has data in it like 0:01:16. What I want to
do is convert these text fields to a time field and keep the data in it so I
can add time to both fields.

:
Sorry, but "general field" is meaningless. There is a
format called General Number, but it does not apply to text
values.

Regardless of that, I really need to know what kind of
number result you want out of this. Do you want the total
number of seconds or what?

If you want it to be a Date/Time data type, then you can
convert it from a text string by using the CDate function.
The Date/Time value can be treated as if it were a number of
type Double, but it would only be the fraction of a day,
which is probably not what you want.

All I know at this point is that you have a text string
"09:00" and you want it to be some kind of number. I can't
tell you how to do something if I don't know what that
simething is.


williamr wrote:
After further review - it's a general field and it look like 09:00


williamr wrote:
I have some information in Excel that expresses the length of a phone call.
It's a text field and when I import it the option for type of field is grayed
out. I would like to run an update query and put that information into
another field and make it a number.


:
What does the text value look like?

What kind of number do you want?