Convert Text Value to Time

[CompassDBA via AccessMonster.com]

I imported a table that has fields that contain time in the format hh:mm:ss.
I need to calculate using the time and cannot do this because it is a text
field. When I try to convert it in the table to a date/time, the records are
deleted. I do not know how to really use VB, so if someone knows how to best
do this using a query then I would really appreciate any suggestion.

Thanks!

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ConvertStringStuff.mdb" which illustrates how to do this
(among other things).

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Douglas J. Steele]

Try the CDate function.

Your best bet is to add a new field to the table and use the CDate function
to update that new field based on the text field. Once you've got all the
times proper, you can delete the text field.

 

[CompassDBA via AccessMonster.com]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ConvertStringStuff.mdb" which illustrates how to do this
(among other things).

I imported a table that has fields that contain time in the format
hh:mm:ss.

[quoted text clipped - 6 lines]

Thanks!

Roger - Thanks for the suggestion. You have a great site. The form that you
have the calculations done on is what I am looking for, but I need it in a
query. A query won't take the same formula that you are using. Do you have
any suggestions? Thanks!

 

[Roger Carlson]

Specifically which formula is not working in a query?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ConvertStringStuff.mdb" which illustrates how to do this
(among other things).

I imported a table that has fields that contain time in the format
hh:mm:ss.

[quoted text clipped - 6 lines]

Thanks!

Roger - Thanks for the suggestion. You have a great site. The form that
you
have the calculations done on is what I am looking for, but I need it in a
query. A query won't take the same formula that you are using. Do you
have
any suggestions? Thanks!

 

[CompassDBA via AccessMonster.com]

Specifically which formula is not working in a query?
[quoted text clipped - 12 lines]

have
any suggestions? Thanks!

Your formula is converting time to string and I am converting string to time.
=ConvertDateToStringTime(ConvertStringTimeToDate([time1])
+ConvertStringTimeToDate([time2])+ConvertStringTimeToDate([time3]),"HH:MM")

I am not using it exactly that way, I just tried the formula
ConvertDateToString just to see if I could get it to work in a query. But I
am converting from Text to time in the format HH:MM:SS. Thanks for your help
with this.

 

[Roger Carlson]

I'm not sure what you're saying, but when I put this:
Expr1: ConvertDateToStringTime(ConvertStringTimeToDate([time1])
+ConvertStringTimeToDate([time2])+ConvertStringTimeToDate([time3]),"HH:MM")

into a query, it works just fine for me.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Specifically which formula is not working in a query?

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ConvertStringStuff.mdb" which illustrates how to do this

[quoted text clipped - 12 lines]

have
any suggestions? Thanks!

Your formula is converting time to string and I am converting string to
time.
=ConvertDateToStringTime(ConvertStringTimeToDate([time1])
+ConvertStringTimeToDate([time2])+ConvertStringTimeToDate([time3]),"HH:MM")

I am not using it exactly that way, I just tried the formula
ConvertDateToString just to see if I could get it to work in a query. But
I
am converting from Text to time in the format HH:MM:SS. Thanks for your
help
with this.

 

[CompassDBA via AccessMonster.com]

I'm not sure what you're saying, but when I put this:
Expr1: ConvertDateToStringTime(ConvertStringTimeToDate([time1])
+ConvertStringTimeToDate([time2])+ConvertStringTimeToDate([time3]),"HH:MM")

into a query, it works just fine for me.
[quoted text clipped - 15 lines]

help
with this.

I get the error "undefined function".

 

[Roger Carlson]

Is the ConvertDateToStringTime() function in a General Module or is it in
behind a form?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I'm not sure what you're saying, but when I put this:
Expr1: ConvertDateToStringTime(ConvertStringTimeToDate([time1])
+ConvertStringTimeToDate([time2])+ConvertStringTimeToDate([time3]),"HH:MM")

into a query, it works just fine for me.

Specifically which formula is not working in a query?

[quoted text clipped - 15 lines]

help
with this.

I get the error "undefined function".

 

[CompassDBA via AccessMonster.com]

Is the ConvertDateToStringTime() function in a General Module or is it in
behind a form?

I'm not sure what you're saying, but when I put this:
Expr1: ConvertDateToStringTime(ConvertStringTimeToDate([time1]) [quoted text clipped - 8 lines]
with this.

I get the error "undefined function".

I am just trying to run it in a query. I am not using a form or anything.

 

[Roger Carlson]

What I'm asking is where did you put the actual function code?

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com

Is the ConvertDateToStringTime() function in a General Module or is it in
behind a form?

I'm not sure what you're saying, but when I put this:
Expr1: ConvertDateToStringTime(ConvertStringTimeToDate([time1])

[quoted text clipped - 8 lines]

with this.
I get the error "undefined function".

I am just trying to run it in a query. I am not using a form or anything.

 

[CompassDBA via AccessMonster.com]

What I'm asking is where did you put the actual function code?

directly in the query field.

 

[Douglas J. Steele]

directly in the query field.

Roger's asking you where the VBA code that defines ConvertDateToStringTime
has been placed.

There are 3 possibilities:

1) In a Module
2) In a Class Module
3) In the Code Module associated with a Form (technically a Class Module)

 

[Roger Carlson]

That's the function CALL. As Doug said, the function itself has to be
stored somewhere. The fact that the error says it's an 'undefined
function', suggests that it is not in a General Module.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[CompassDBA via AccessMonster.com]

That's the function CALL. As Doug said, the function itself has to be
stored somewhere. The fact that the error says it's an 'undefined
function', suggests that it is not in a General Module.

Right now, all I have in my database is one table and one query that is
simply putting the formula New: ConvertDateToStringTime
(ConvertStringTimeToDate([DE_CallTime]),"HH:MM") into a field.

 

[Douglas J. Steele]

That's the function CALL. As Doug said, the function itself has to be
stored somewhere. The fact that the error says it's an 'undefined
function', suggests that it is not in a General Module.

Right now, all I have in my database is one table and one query that is
simply putting the formula New: ConvertDateToStringTime
(ConvertStringTimeToDate([DE_CallTime]),"HH:MM") into a field.

In other words, you have not copied the VBA from Roger's database into your
own?

ConvertStringTimeToDate does not exist in VBA: it's a function you have to
define.

 

[CompassDBA via AccessMonster.com]

[quoted text clipped - 7 lines]

simply putting the formula New: ConvertDateToStringTime
(ConvertStringTimeToDate([DE_CallTime]),"HH:MM") into a field.

In other words, you have not copied the VBA from Roger's database into your
own?

ConvertStringTimeToDate does not exist in VBA: it's a function you have to
define.

Thank you for all of your help.