Only Show a specific part of a text field 2

[Bennie47250]

Using Crystal 7.0
Data is from a MS SQL table


Have a text field that has more information in it than I want. How can I only show the part I want?

In the example below, I only want to read the last 3 prompts which is

PROMPT;Month for the report;7
PROMPT;Days for calculating;145
PROMPT;Cust Num;15881

Everything else I do not want it to appear on the report.

I have 3 different report and each have their own unique prompts but what I’m looking for always comes after the text EXP_FORMAT;31

Example of all the text in the field
PROMPT;DATETIME;8/4/2011 8:58:42 AM
PROMPT;DATE;8/4/2011
PROMPT;TIME;8:58:42 AM
PROMPT;USERID;Dollar
PROMPT;UserName;DollarUSER
PROMPT;UserEmail;
PROMPT;UserPhone;
PROMPT;UserFax;
PROMPT;UserLastLogin;8/4/2011 8:58:42 AM
PROMPT;UserData1;
PROMPT;UserData2;
PROMPT;UserData3;
PROMPT;UserData4;
PROMPT;UserData5;
LOGONALL;DSN=OLAP;

EXP_FORMAT;31

PROMPT;Month for the report;7
PROMPT;Days for calculating;145
PROMPT;Cust Num;15881

Thanks
Bennie

 

[briangriffin]

If it always comes after EXP_FORMAT;31 and that is always the exact string, then this will work:

mid({yourtextfield},instr({yourtextfield},"EXP_FORMAT;31") + 13)

 

[lbass]

I think that should be +14, not sure, but there also appears to be a couple of returns, so you might try +16.

-LB

 

[Bennie47250]

Will test this when I get back from lunch.

What is the +13, +14 or +16 for anyway?

The section after the EXP_FORMAT;31 may have 2, 3 or 6 lines of data

Thanks
Bennie

 

[lbass]

The + number is just to get you to the beginning of the section you want to see. instr() returns the position of the first character in the specified string, in this case the position of "E" in "EXP_FORMAT;31". "1" is the 13th characters. Additional returns (chr(13)) would add 1 character each. Just play with it. The mid function used as Brian shows will then show whatever is left in the string--as long as you don't specify you only want a certain number of characters after the beginning of the string. For example, for the string:

"Hello, Boston, on August 4"

mid({table.string},instr({table.string})+8)

...would return:

"Boston, on August 4"

mid({table.string},instr({table.string})+8, 6)

...would return:

"Boston"

-LB

 

[Bennie47250]

Well I just couldn't wait till after lunch and tried it now.

Getting the error message "Blob Field or Memo Field cannot be used in a formula".

Funny SQL is reporting this filed as a text field...

Any thoughts on how to work around this?

Thanks

 

[lbass]

You really need to upgrade!

Do you have SQL expression as an option in your field explorer?

-LB

 

[Bennie47250]

You are correct, wish we could do an upgrade.

It appears I do have SQL Expression. In 7.0 if I go to insert, one of the options is "SQL Expression"

 

[Bennie47250]

Oh and by the way, don't know how to use it so can somonmen tell me how to use to to get around my issue?

 

[lbass]

You can help me (since I don't know what your datasource supports) by going into the function list in the SQL expression editor and tell me whether you see LEFT() or LOCATE() or SUBSTRING under the string category.

-LB

 

[Bennie47250]

there is Locate(,) Locate(,,) Left(,) and Substring(,,) among others

 

[lbass]

Okay, try this SQL expression:

{fn Substring("table"."memo",{fn locate('EXP_FORMAT;31',"table"."memo")}+16)}

The field you want to use in the SQL expression will probably NOT be in the field list, so start by adding another string field in the function--just so you can see what punctuation should be used (it might not be double quotes), and then manually type in your actual table and field name, using the correct punctuation. Be sure to use single quotes around the EXP_FORMAT;31 though.

-LB

 

[Bennie47250]

Don't think this old version of Crystal is going to allow this. The field I want to perform this formula on is not listed in the field list of the expression builder. If I manually enter the field name, Crystal returns the error

Error is compiling SQL Expression, Invalid field found here.

 

[lbass]

Hmm. In older versions (8.0) I have done this exact thing, so I wonder if you entered it correctly? Please paste in what you tried.

-LB

 

[Bennie47250]

First, Thanks for keeping with me lbass.

Remember that the field I'm wanting to use is not listed so I bring in another field to get the proper formatting of the table and field, then replace the field with the field I want to use.

{fn SUBSTRING(RequestLog."ReqParms",{fn LOCATE('EXP_FORMAT;31', RequestLog."ReqParms")}+16)}

To check my syntax, I used a different function, but still get the same error

{fn LEFT(RequestLog."ReqParms", 3)}

Now if I perform this Left function on any of the fields listed it works fine.

 

[lbass]

Well, if you have entered the punctuation correctly, then I guess it won't work. I might try placing double quotes around the table name, too.

{fn LEFT("RequestLog"."ReqParms", 3)}

I know I have done this in 8.0, however, but maybe 7.0 can't handle this.

-LB

 

[Bennie47250]

Thanks for your help lbass and briangriffin.

While this will not work for this table in Crystal, was able to take what I learned here and use it in Access.

Plus I learned something new today. Pretty cool way to use mid and instr