Find date in TEXT field & remove if older than current date

emulsified

Does anyone have any suggestions on how to search a TEXT field for any date, check it against the current date to see if it is in the past or older than the current date?

I have a TEXT field on my item card named ETADate that we use to put information like "ETA 12/20/2010" or "ETA 12-20-10 NEW ITEM".

To clarify things I will be setting to blank when an older date is found:

ETADate := '';
// Save the modification, commit blah blah

I'm going to create a processing only report to clean up old ETADate information and was wondering if anyone had an easy way to find an older date in a string?

Albertvh

You could try something like this
This assuming that the Date portion is after the first blank.

DateStr := ETAField;
IF STRPOS(DateStr,' ') > 0 THEN BEGIN
   DateStr := COPYSTR(DateStr,STRPOS(DAteStr,' '); 
   IF STRPOS(DateStr,' ') > 0 THEN BEGIN
      DateStr := COPYSTR(DateStr,1,STRPOS(DAteStr,' ') ;
END;
DateStr := DELCHR(DateStr,'=',DELCHR(DateStr,'=','0123456789'); //This will remove all non-numerics
IF EVALUATE(DateVar,DateStr) THEN
  IF DateVar <= TODAY THEN
   ETAField := ''; 

DateStr is Text and DateVar is Date

Hope this points you in the right direction

emulsified

I haven't tried your suggestion yet, but let me see if I understand your code correctly:

1. In your code you are saying IF the position of the first blank space is greater than 0 then copy from that position into DateStr.
2. So DateStr was "ETA 12-10-10 NEW ITEM" and would become "12-10-10 NEW ITEM".
3. Then you run DateStr through again and it copies from position 1 of DateStr to the position of the next blank space so I should end up with "12-10-10".
4. After that it looks like you're using DELCHR to remove anything that is not number like the "-" hyphens or "/" slashes normally found in date strings.
5. Finally you are using EVALUATE on DateStr which is currently "121010" to put it into DateVar as a valid date for comparison.

Did I follow this correctly?

I think I got it now, but what it I have blank spaces at the beginning of the field and also before and after the actual date?

I'm going to give this a try:

DateStr := ETAField;

IF STRPOS(DateStr,' ') > 0 THEN BEGIN
   DateStr := COPYSTR(DateStr,STRPOS(DAteStr,' ')); 
   IF STRPOS(DateStr,' ') > 0 THEN BEGIN
      DateStr := COPYSTR(DateStr,1,STRPOS(DAteStr,' '));
END;
DateStr := DELCHR(DateStr,'=',' '));  // added to remove any additional whitespaces before or after the date, but I guess the next line would do that anyway
DateStr := DELCHR(DateStr,'=',DELCHR(DateStr,'=','0123456789')); //This will remove all non-numerics
IF EVALUATE(DateVar,DateStr) THEN
  IF DateVar <= TODAY THEN
   ETAField := ''; 

// commit

Savatage

if spaces are an issue you can find the first dash or slash & count back 2 positions

Albertvh

You are spot on but the delchr line will delete anything that is not numeric so there will be no spaces either.
If there are spaces in the begining of the field you can replace the datestr := statement to
DateStr := DLECHR(ETAField,'<>',' ');
This will delete all the leading and trailing spaces.

DenSter

Why don't you make that field a date type field, and add a field called "ETA Comment" or something like that. It will be much easier to program that way, plus you will have the capability to filter.