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 Chriss Miller 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
- Thread starter Melzer
- Start date
-
1
- #2
synapsevampire
Programmer
Change it on the database side, there's no solution within Crystal.
If you're using a View or stored procedure, parse it into separate fields or truncate it if you can.
-k
kai@informeddatadecisions.com
If you're using a View or stored procedure, parse it into separate fields or truncate it if you can.
-k
kai@informeddatadecisions.com
-
3
- #3
Roadkill150
Programmer
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
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
Roadkill150
Programmer
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
-Bruce Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com
synapsevampire
Programmer
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
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
synapsevampire
Programmer
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
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
synapsevampire
Programmer
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
Thanks again, Roadkill, I was told by someone here that this won't work, but you disproved that.
-k kai@informeddatadecisions.com
- Thread starter
- #8
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.
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.
synapsevampire
Programmer
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
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
- Thread starter
- #11
synapsevampire
Programmer
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
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
synapsevampire
Programmer
Ken: No surprise that Access wouldn't work, it's not really a SQL database.
-k
kai@informeddatadecisions.com
-k
kai@informeddatadecisions.com
- Thread starter
- #15
synapsevampire
Programmer
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
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
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
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
synapsevampire
Programmer
Ken: True, and the CR ODBC driver seems more forgiving, but melzer also tried it natively, as did I with success.
-k kai@informeddatadecisions.com
-k kai@informeddatadecisions.com
AndrewBaines
Technical User
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
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
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
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
- Status
Similar threads
- Locked
- Question
- Locked
- Question