need to pull data from text field

[joeengler]

I have a text field from a SQL database that contains the following '(1332) Call WFJN: 60161 WFWO: 02NCR_000 OCA AP Checks' I need to pull out the job number which is between the 'WFJN:' and 'WFWO'

Database = SQL
Crystal = 10.0
Field name = summary
Table = Call

 

[lbass]

If the job number is always 5 digits, then use this:

mid({call.summary},instr({call.summary},":")+2,5)

If the length of the job number varies or if there is sometimes no colon in the string, please show variations.

-LB

 

[joeengler]

Sorry,

The job number can be 4,5 or 6 digits

(1332) Call WFJN: 60161 WFWO: 02NCR_000 OCA AP Checks
(1341) Call WFJN: 5374 WFWO: 02NCR_000 OCA AP Checks
(1339) Call WFJN: 715420 WFWO: 02NCR_000 OCA AP Checks

 

[lbass]

Try:

stringvar x := trim(extractstring({call.summary},":",":"));
left(x,instr(x," ")-1)

-LB

 

[joeengler]

I get an error while processing records,

"Stgring Length is less then 0 or not an integar

I tried adding a if statement to make sure I'm only looking at records with this summary

if {Call.Summary} like '*Call WFJN:*' then
stringvar x := trim(extractstring({call.summary},":",":"));
left(x,instr(x," ")-1)

 

[lbass]

I think you need to check for inconsistent use of the colon in the field. Try:

stringvar x := trim(extractstring({call.summary},":",":"));
if isnumeric(trim(left(x,instr(x," ")-1))) and
len(left(x,instr(x," ")-1)) >= 4 then
left(x,instr(x," ")-1);

-LB

 

[joeengler]

Thanks for all the help so far,

I still get the same error, this is what crystal is highlighting as the problem

left(x,instr(x," ")-1)

 

[lbass]

Can the field be null? Try:

stringvar x;
if isnull({call.summary}) then
x := "Null" else
x := trim(extractstring({call.summary},":",":"));
if isnumeric(trim(left(x,instr(x," ")-1))) and
len(left(x,instr(x," ")-1)) >= 4 then
left(x,instr(x," ")-1) else
""

-LB

 

[joeengler]

Working now Thanks for all the help