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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

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/...UBED1=ACCESS-L


"CompassDBA via AccessMonster.com" <u30801@uwe> wrote in message
news:6cde19a27032a@uwe...
>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!
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...ccess/200701/1
>

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"CompassDBA via AccessMonster.com" <u30801@uwe> wrote in message
news:6cde19a27032a@uwe...
>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!
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...ccess/200701/1
>

Roger Carlson wrote:
>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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

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/...UBED1=ACCESS-L

"CompassDBA via AccessMonster.com" <u30801@uwe> wrote in message
news:6d0370ec2d35d@uwe...
> Roger Carlson wrote:
>>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!
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...ccess/200701/1
>

Roger Carlson wrote:
>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.

--
Message posted via http://www.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.

--
--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/...UBED1=ACCESS-L

"CompassDBA via AccessMonster.com" <u30801@uwe> wrote in message
news:6d100389928c1@uwe...
> Roger Carlson wrote:
>>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.
>
> --
> Message posted via http://www.accessmonster.com
>

Roger Carlson wrote:
>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".

--
Message posted via http://www.accessmonster.com

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/...UBED1=ACCESS-L

"CompassDBA via AccessMonster.com" <u30801@uwe> wrote in message
news:6d11b10af6e09@uwe...
> Roger Carlson wrote:
>>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".
>
> --
> Message posted via http://www.accessmonster.com
>

Roger Carlson wrote:
>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.

--
Message posted via http://www.accessmonster.com