Try lookin at these calculated fields, you should be able to carry on withthe theme and get all of the fields you need.
You need to trap the whole line as on field and call it orig. Posit bcomes the lock for the extracting, and the rest is fun.
instr("/",orig) Use this on to fix a point
substr(orig,1,posit-3) This should give you the name.
substr(orig,posit-2,5) This will give you the period
And this one will do the Purchase or sale field
Here is something else to experiment with using the FLOATING TRAP and a few ideas along the same lines as Nigel's suggestion.
Firstly, to make life easier working with the floating trap, create a DUMMY line where each part of the line is as wide as it is ever likely to be in a real report.
Different Customer 12/03 Purchase 12345 BIT @ 9999.99 Comment 999.99 Ball 1000.00[/font][/quote]or whatever is appropriate. Use this as the SAMPLE text in your template.
For the above line try the following traps.
Alpha first character (to eliminate blank lines).
Blank traps for the SECOND space (end of the customer name) and NUMERIC for the number immediately following.
Blank trap at the end of the 12/03 with an ALPHA trap immediately following for the start of the word PURCHASE.
Blank trap at the end of PURCHASE followed by a NUMERIC trap for 12345. Blank trap at the end of the number.
NUMERIC trap at the start of the 120.30 number, BLANK trap at the end immediately followed by an ALPHA trap for the C in COMMENT.
If there were always at least 2 fields there you could also set a BLANK trap after 999.99 immediately followed by an ALPHA trap for BALL. However in this case it will produce a trap that will skip some lines where only one field exists.
You should now be able to 'paint' all the fields you require EXCEPT that I think you will need to create a single field to include both "Comment 999.99" and "Ball 1000.00" since it is not possible to easily identify what the last 2 values are for. Preceding string functionality is not available when floating traps are used. However you should be able to create a couple of calculated fields and do some conditional processing and splitting to extract the values you require to the right columns.
For example, if we call those fields VALUES and you want to create a field for the value of COMMENT.
For Example a calculated NUMERIC field set to 2 decimal places with the following formula would, in V7, give the values for "Comment".
and for BALL
would work well.
LSPLIT, RSPLIT, IF(), LEFT, RIGHT, INSTR and SUBSTR might also be valuable functions to consider, especially for users of earlier versions who may not have the EXTRACT function available. Of course the same comment goes for the entire suggestion if you don't have the Floating Trap feature available!
What do you think? Does it work for your real report? It may not.
[size="1"][ September 09, 2004, 06:47 PM: Message edited by: Grant Perkins ][/size]