Need to "Extract" Date & Time from String entered as free text

[Never2L8]

I need to isolate the date and time from a string that is quite varied. I would like to place the date and time in 2 separate columns. The recipient of this report would like to sort data based on these 2 columns (fields).

The problem is that this string (let's call it CRITICAL CALLS) consists of a combination of coded entry and free text entry. The format is highly varied. For example:

CLRB-;MARY RN, CCU ON 7/20/09 9:30 AM JD.

CLRB-;AGATHA RN TELE UNIT 072009@0930 AS

REP-CLRB-;DR SMITH 7.20.09 930AM DJ

CLRB-; TO MARY RN BY DJ @ 930AM 7/20/09.

As you can see, there is no consistency in order or delimiters. I need to pull out elements that look like numbers within the string and then convert them to Date and Time.

Any suggestions on how to go about this? I would greatly appreciate any help!

TIA

 

[Madawc]

No simple method I can see. Your first step should be to split the string using the SPLIT command. You'd then need to check each portion separately: UBOUND gives the number of table elements.

Depending on your version of Crystal, you may be able to use IsDate, IsTime and IsDateTime to identify and convert. (It always helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever.) Take advantage of Crystal's flexability, develope your code against the data rather than hoping to fully design it in advance.

At best you'll get logic that works most of the time. I'd show it along with the raw input, allowing manual correction.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP