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!

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

Long string fields 4

Status
Not open for further replies.
Melzer

Melzer

Technical User
Jul 30, 2001
11
US
Ever had a field in one of your tables that is a long string varchar. Ends up you can't select of create a formula in Crystal Reports on these fields. Anybody have any advice?
 
Sort by date Sort by votes
Hello,
Crystal treats fields over the length of 254 chars as memo fields, which cannot be used in formulas, grouping, etc. The solution to this is:

To use a memo field in formulas, parameters, grouping, or sorting, the field must first be truncated into sections of 254 characters or less.

There are 2 ways to do this:

A. Truncate the field in the CR Designer.

- or -

B. Truncate the field in the database using a database view.


A. To truncate the field in CR, there must either be an ODBC connection to the database or a
native connection to a SQL type database. For more information on SQL type databases, see the "Understanding Databases" Chapter of the Crystal Reports User's Guide.

To truncate the field in CR, complete the following steps:

1. On the 'Insert' menu click 'Field Object'

2. Click 'SQL Expression Field' and then click the 'New' button.

3. Type a formula field name (such as "part1") and then type a formula similar to the following. Be aware that the memo field is not visible for selection in the Report Fields list. It must be typed into the SQL Expression Editor Box.

{fn SUBSTRING(table.`memo field`,1 ,254 )}

This formula returns the first 254 characters of the memo field. To return the next 254 characters of the memo field, create another SQL expression field to return character 255 to character 510. Create as many SQL expression fields as required to return all the characters from the memo field.

4. On the 'Insert' menu click 'Field Object'

5. Click 'Formula Field' and then click the 'New' button.

6. Type a formula field name and then type formula similar to the following:

//@concatenate
//this formula concatenates
//the SQL expression fields
//together
{%part1} + {%part2} + {%part3}

The formula @concatenate can be used in formulas, parameters, grouping, or sorting.

Hope this helps!
-Bruce Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com
 
Upvote 0 Downvote
Just a sidenote.. There are almost always workarounds for just about anything in crystal, it just takes a bit of time to find them =) I learned a long time ago to almost never say "It can't be done" with crystal.
-Bruce Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com
 
Upvote 0 Downvote
Very interesting, Roadkill...

What database do you do this with? I'd tried to work out the syntax for this before without success... I normally do this sort of thing on the database, but it'd be helpful to know how this works.

Unfortunately, it doesn't seem to work in my Oracle environment, even though I changed the syntax to SUBSTR instead of SUBSTRING.

My data type, as is the type for this thread question, is a varchar over 255, but if this does work for memos (or varchars), It'd be very helpful.

BTW, I can do this with a field that's under 255 chars without any problems.

-k kai@informeddatadecisions.com
 
Upvote 0 Downvote
Ahhh, I found the problem, the native connection doesn't work, the CR ODBC for Oracle does. They article you copied said that it works, but it doesn't for me...

Good to know that the ODBC works i case I ever use it, thanks Roadkill.

Here's the CR doc that Roadkill supplied:


There are a few other KB articles regarding this on the Crystaldecisions KB:


-k kai@informeddatadecisions.com
 
Upvote 0 Downvote
OK, I take that back, it failed when I was using an existing report, but when I tried it again, even the native worked.

Thanks again, Roadkill, I was told by someone here that this won't work, but you disproved that.

-k kai@informeddatadecisions.com
 
Upvote 0 Downvote
Thanks for everyone's help.

I tried the above and when I go to save in the SQL Expression Editor I get the following messages:

Error in initializing SQL Expression parser.


Error in creating SQL Expression COM parser.

Can anybody help?
 
Upvote 0 Downvote
Dear Melzer,

Roadkills's example shows with single quotes. When I tried it I also got an error message so I switched to double quotes and it worked:

{fn SUBSTRING(Help_Desk."Problem Description",1 ,254 )}

What I did was insert a field so that I could see how Crystal wanted the field name formatted.

Try that and see if it will work.

Thanks also to roadkill (gave you your star :)), your post helped me to solve a problem for searching on a blob field using a parameter. It now works like a dream....I just search all the parts and what's even better is it is getting passed onto the searver!

ro

Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
 
Upvote 0 Downvote
Perhaps it's database dependent, or Crystal may have incorrectly used single quotes in their example (I doubt that Roadkill modified the Crystal KB article he shared).

Crystal with Oracle 8 creates it as follows:

SUBSTR("MyTable"."MyField",1 ,254 )

I tested with single quotes again, and it failed, though without quotes it does succeed.

Previously I posted that Oracle errored out on my try with an existing report, but once I created a SQL Expression in a new report, then switched back to my existing report, it would allow me to do it.

Far from an exact science, but doable.

-k kai@informeddatadecisions.com
 
Upvote 0 Downvote
Thanks, but I tried all of that and I still get the error message.
 
Upvote 0 Downvote
Thank you RoadKill for spotting that KB article. I wonder why nobody noticed that before? This has been discussed in several threads and FAQs. I have updated my FAQ on memo fields to include the following KB article, which is different from the one mentioned above:


Note that all SQL expressions are database specific, with the syntax and available functions changing with your ODBC or database driver. I was able to use the workaround with SQL Server v7, but not with MS Access data via ODBC.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
 
Upvote 0 Downvote
I'm using Crystal 8.5 and oracle database (8.1.7). I'm logging in directly to the oracle server. I've tried using the OBDC as an alternative but I still continue to get the above errors.

 
Upvote 0 Downvote
Try it with a new report, and at 1st, just try to use a SUBSTR on a varchar which is under 255.

If that doesn't work, then I'd guess that it's your syntax, because you have the same setup as I do.

I did get strange errors at 1st, but I managed to get it working.

-k kai@informeddatadecisions.com
 
Upvote 0 Downvote
Might also be different ODBC drivers? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
 
Upvote 0 Downvote
Access via ODBC should work - it has the syntax available.
However, when I tried
{fn SUBSTRING(Product_Type.`Product Type Name`,1 ,255 )}
I get a GPF. I'm running XP which does seem to give me grief with some ODBC connections. Perhaps someone else could try this.
Crystal won't give you the memo in the SQL function editor, you'll need to type it manually. Andrew Baines
Chase International
 
Upvote 0 Downvote
I am on Win2K and I tried typing it in manually. The function is there, and it didn't GPF, but it told me there was an error. If anyone successfully does this with MS Access please post your SQL Statement here. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

LauraW
  • Locked
  • Question
Format Numbers in a String
Replies
4
Views
387
LauraW
LauraW
Hellak
  • Locked
  • Question
Simple report - filtering by comparing two fields
Replies
1
Views
152
pmax9999
pmax9999
GCL2007
  • Locked
  • Question
Extracting from string 1
Replies
8
Views
220
GCL2007
GCL2007
VHDN
  • Locked
  • Question
Unable to drag database fields into a formula
Replies
1
Views
150
VHDN
VHDN
GCL2007
  • Locked
  • Question
Date fields coming into Crystal as Date/Time 1
Replies
2
Views
151
GCL2007
GCL2007

Part and Inventory Search

Sponsor

Back
Top