Number of Instances of string in A Memo Field

[wildsharkuk]

Hi I'm searching a memo field for the number of instances the string "------------" appears in the memo field. Is there any formula that would help me achieve this?

Unfortunately the database stores when the record was updated but in a history (memo) field.

I'm currently using CR 10 and the Database is SQL Server.

Thanks

Wildsharkuk

 

[vidru]

I think this formula might work for you:

NumberVar LastPos := 1;
NumberVar Cnt := 0;
StringVar SearchStr := "------------";

If InStr({Table.MemoField}, SearchStr ) > 0 Then
  While LastPos <> 0 Do(
    Cnt := Cnt + 1;
    LastPos := InStr(LastPos + 1, {Table.MemoField}, SearchStr);
  );

Cnt;

Another, less verbose way would be to use the Split function:

StringVar SearchStr := "------------";
If InStr({Table.MemoField}, SearchStr) > 0 Then
  Count(Split({Table.MemoField}, SearchStr)) - 1
Else
  0;

-dave

 

[wildsharkuk]

Hi Vidru,

Thanks the formula worked perfectly.

My second question is

This memo field stores 3 dates for each update with the same format eg

"Date Opened = 01/03/2005
Date Req'd = 15/03/2005
Date Comp'd = 02/03/2005
------------
Date Opened = 28/02/2005
Date Req'd = 12/03/2005
Date Comp'd = 01/03/2005

etc..."

would it be possible to retrieve each 'Opened Date' in the Memo field and report on these - find out how many times a file has been updated in a given period. I would add a date range parameter for the user to select the date range.

Thanks

wildshark

 

[wildsharkuk]

Hi all,

Thanks for your suggestions - this initially helped me to create the required report. I need to slightly modify the code so that it reads from the end of the file to the beginning (to get the latest dates).

NumberVar LastPos := 1;
NumberVar Cnt := 0;
StringVar SearchStr := "------------";

If InStr({Table.MemoField}, SearchStr ) > 0 Then
While LastPos <> 0 Do(
Cnt := Cnt + 1;
LastPos := InStr(LastPos + 1, {Table.MemoField}, SearchStr);
);

Cnt;


"Date Opened = 01/03/2005
Date Req'd = 15/03/2005
Date Comp'd = 02/03/2005
------------
Date Opened = 28/02/2005
Date Req'd = 12/03/2005
Date Comp'd = 01/03/2005

etc..."

so on the current record it would return 01/03/2005 as the latest date not the other way round.

Any help on this would be greatly appreciated.

wilsharkuk