Converting string field to time field 1

[gingerbagpuss99]

I have Crystal 8.5 and am trying to select records between certain times. Unfortunately the field is a string.

I have tested the field to see if it can be converted to a time with Istime ({field}) but this returns false. I have created the following formula to break the time up but I can't seem to put it (all together with separators) and then be able to use this to filter on the time. I'm not sure I'm on the right track.

( (Mid ( ({field}) , 5 ,4 ) ),
(Mid ( ({field}) , 3 ,2 ) ),
(Mid ( ({field}), 1 ,2 ) ) )

Thanks for your help.

 

[BobSuruncle]

It would help if we could see what the data actually looks like, and what your formula is written to do.
I'm assuming that you want to parse the string and build a Time from it.
If that's the case, the Time function needs numbers to work with, not strings.
Change your example slightly to:

Time( ToNumber( (Mid (  ({field})  , 5 ,4 ) )  ),
ToNumber( (Mid (   ({field})  , 3 ,2 ) )  ),
ToNumber( (Mid (  ({field}), 1 ,2 )  ) ) )

Bob Suruncle

 

[gingerbagpuss99]

Thanks very much for your advice

The formula as follows returns the error - remaining text does not appear to be part of the formula.

Time (ToNumber (Mid ( ({Clinical_record_N.Time_Logged}) , 5 ,4 ) ) ),

ToNumber ( (Mid ( ({Clinical_record_N.Time_Logged}) , 3 ,2 ) ) ),

ToNumber ( (Mid ( ({Clinical_record_N.Time_Logged}), 1 ,2 ) ) ) )

The field data looks like 150500 ie 3.05.00 pm. Any help?

 

[lbass]

I think your formula should look like:

time(left({Clinical_record_N.Time_Logged},2),mid({Clinical_record_N.Time_Logged},3,2),right({Clinical_record_N.Time_Logged},2))

-LB

 

[gingerbagpuss99]

Thanks for that - it says there are too many arguements given to the function ???? Not sure what to do really.

 

[lbass]

Sorry, that should have been:

time(val(left({Clinical_record_N.Time_Logged},2)),val(mid({Clinical_record_N.Time_Logged},3,2)),val(right({Clinical_record_N.Time_Logged},2)))

-LB

 

[gingerbagpuss99]

Excellent. Thank you very much. Sarah