Time Conversion Problem 1

[arcangelca]

Hello All,
New here to the thread, will probably have to use it often.
This may be a simple problem, but I'm having issues with it, so I'm hoping someone can help.
I'm having a problem parsing out some data from an access database using crystal reports. It is time data, but isn't in the format of time.
I'm using Crystal reports 7.0 if this is needed.
Here is some sample data
1 (meaning 00:01:00)
435 (meaning 04:35:00)
1329 (meaning 13:29:00)

Now I am able to extract the numbers using the right() Left() formulas to get it to look like a time, but the program won't recognize it as a time. After I get the time done, I have to use the datetime(date, time) to make a real date and time field.
So my question is this,
Is there a better way to get the time data converted into a real time value so crystal will recognize it?
I hope this isn't a stupid question for all of you, but I've had to learn this program on my own, and want to use it more extensively.

thanx in advance

 

[synapsevampire]

Since it's an Access database, my suggestion is to let Access do it within an Access Query, and then use the Query as the report data source.

You can do it in Crystal, and I'll assume that it's stored as a number not a string (an important thing to include):

numbervar TheTime:= 1329; // replace with your field
if len(totext(TheTime,0,"")) < 1 then
time("00:00:00")
else
if len(totext(TheTime,0,"")) < 2 then
time("00:0"+totext(TheTime,0)+":00")
else
if len(totext(TheTime,0,"")) < 3 then
time("00:"+totext(TheTime,0)+":00")
else
if len(totext(TheTime,0,"")) < 4 then
time(val(left(totext(TheTime,0),1)),val(mid(totext(TheTime,0,""),2)),0)
else
if len(totext(TheTime,0,"")) < 5 then
time(val(left(totext(TheTime,0,""),2)),val(mid(totext(TheTime,0,""),2,2)),0)

Note that I demonstrated using TIME with either strings or numerics here.

-k

 

[kskid]

Try this

//Assuming time field is not null
//@timex
stringvar tm := totext({table.timenumber},"0000");
numbervar hr := tonumber(mid(tm,1,2));
numbervar mi := tonumber(mid(tm,3,2));

time(hr,mi,0)


for date and time

datetime({table.date},{@timex})

 

[synapsevampire]

Looks much slicker, *Wicthita*!

-k

 

[arcangelca]

Thanks guys,
Much appreciated.
I just talked to someone else here at work, and they suggested doing it in access as well.
I'll give them all a try and see which works better.

Again, Thanks

 

[arcangelca]

Wichita,
It worked great.
Thanx again