Splitting A Memo Record Into Multiple Rows

[braves90]

Hello, I've been searching Google for the last couple hours and can't seem to make anything work. What I'm trying to do is to split a record which contains dates in the format YYYYDDD, where YYYY is the year (2016) and the DDD is the number of the day of the year. This is followed by HHMMHHMM, but normally is 00000000 followed by the next date.

So the record that I'm trying to split ends up looking like 201414500000000201536400000000201601600000000........ you get the picture. There are 50 or more dates that are contained in this field. These dates then need to be converted into a consumable form by CR and contained in an array.

What I want to do is display any dates contained in that Memo record which occur within the next two weeks from when the report is generated.

I've been trying to use the SPLIT function in conjunction with ISNUMBER and DATETIME (or something similar, I don't have my file with me) but keep getting an error telling me to use subscripts, which I did.

I had another field that was like this which I used a Command and created 48 fields. This may not always work since the number of dates contained in the record changes.

Any thoughts or advice would be appreciated, thanl you.

 

[CrystalizeCanada]

Below is an example that doesn't use the SPLIT function although using the SPLIT function works too as long as the dates are followed by '0000000' and no one is filling in the hours and minutes after a date.

//PURPOSE OF FORMULA: To parse a memo field, extract dates and check for dates in a range
//NOTES: Formula can be expanded to extract the time but the formula below doesn't extract the time
//TIMING OF FORMULA:
WhileReadingRecords;
//VARIABLE DECLARATIONS:
StringVar YourMemoField;
NumberVar i; //Used in the loop
numberVar start; //Used in the loop
numberVar y; //Used for the year
numberVar d; //Used for the number of days
dateTimeVar theDate; //The date that has been extracted from the memo
dateTimeVar ComparisonDate;
stringVar YourDateMatches;
//VARIABLE ASSIGNMENTS:
YourMemoField := '201414500000000201536400000000201601600000000'; //Use your string field here
ComparisonDate := Date(2014, 01, 01); //Use your date field here
YourDateMatches := ''; //reset the variable
For i := 1 to Length(YourMemoField)/15 do //15 is the length of each datetime
(
start := if i = 1 then 1 else start + 15;
y := ToNumber(YourMemoField [start to start + 3]);
d := ToNumber(YourMemoField [start + 4 to start + 6]);
theDate := DateAdd("d", d, Date(y,1,1));
if theDate in ComparisonDate to ComparisonDate + 14 then //plus 14 to add 2 weeks
(
YourDateMatches := YourDateMatches & ", " & totext(theDate,"dd MMM yyyy")
);
);
//FORMULA RESULT:
Replace(YourDateMatches, ", ", ""); //This replaces the last one

Gordon BOCP
Crystalize

 

[braves90]

Thank you for the response. I will try this tomorrow as I am interested in seeing multiple solutions to the same problem. I eventually got the SQL Command to work using a combination of to_date, substr, and concat. After that I put the dates in an array and had each of the days checked if they were in the array.