Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Number of Instances of string in A Memo Field

Status
Not open for further replies.

wildsharkuk

Programmer
Mar 12, 2004
36
GB
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
 
I think this formula might work for you:
Code:
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:
Code:
StringVar SearchStr := "------------";
If InStr({Table.MemoField}, SearchStr) > 0 Then
  Count(Split({Table.MemoField}, SearchStr)) - 1
Else
  0;
-dave
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top