Strip off 4 characters at the end of a Text field.

[gbs01]

I import this text string into my text database field;

Field Name : Call Arrival Date
Value : 2005-02-28 08:29:14.107

How do I get rid of the .107 on the end?

I would like to enter the formula in my query grid.


Thanks in advance for your help!
jalexander

 

[lupins46]

In the 'update to' row of your update query put:

left([call arrival date],len([call arrival date])-4)

This assumes that you always have 3 digits after the decimal point.

 

[traingamer]

And if it's NOT always 3 characters, but is the only decimal point:

left([call arrival date],InStr([call arrival date], ".") - 1)

(Get everything up to, but not including, the first "."

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[gbs01]

Works great! Both options.

How do I now subtract one Date/Time from another and get accurate Date/Time results?
or
Can I also add a formula to write these new values(w/o the .133) to replace my original text values in my table?

And also change the data type to Date/Time in my table?

In the query I tried to use the Format command in the Properties field but it won't subtract properly in Dates & Times. (I guess because the data is really text?)

Here are my table details:
-------------------------- Sample Values
CallArrival Text 10 2005-02-24 16:14:40.133
CallCompletion Text 10 2005-02-24 16:15:11.137

Thanks again!
jalexander

 

[PHV]

In the query grid window:
ElapsedTime: CDate(Left(CallCompletion,InStr(CallCompletion,'.')-1)) - CDate(Left(CallArrival,InStr(CallArrival,'.')-1))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gbs01]

Thanks! You guys always make me look better to my boss than I really am!

So, Cdate = Convert text date to a regular date?
If so, that is much better, than trying to go back & update my table data.

Thanks again!
Jalexander