Replace function does not work with ""

[lbass]

I have noticed in other threads the use of the replace function to close spaces, as in:

replace({table.string}," ", "&quot

replace({table.string},"a", "&quot

When I try this, the string disappears altogether. In the second example, if I insert a space between the quotes the string reappears, but with the space in place:

"happy" becomes "h ppy"

I am using CR 8.0. Is this a known issue for earlier versions? I couldn't find anything on the Crystal Decisions website about this. Logically, it would make sense that replace would have to replace a character WITH something, even a space, rather than removing a space altogether; however, when I want to eliminate a space, I can't seem to discover any way of doing it.

-LB

 

[Ngolem]

try it with single quotes instead of double ones

replace({table.string},"a", '')



Jim Broadbent

 

[lbass]

Hi Jim,

Thanks for responding. I already tried that, with the same result--it doesn't work. I'm wondering if the replace function actually does work for other people to remove spaces--I guess it does. I just wonder if it's my version that's the problem.

Has anyone else had this problem?

-LB

 

[Ngolem]

I only have CR7 at home and Replace isn't there as a function

It would not be something stupid like plugging in your default value if you have that option set to a blank space, would it?

Jim Broadbent

 

[lbass]

No, I'm not using any formula that sets nulls to a blank space, and I don't have "convert nulls to default" checked. This is just using "replace" with a regular database string field, and this happens with the Xtreme database as well as when I use my own database. I can create a formula:

replace({table.field},"a","&quot

When I place it on the report canvas, nothing appears. If I go back and add a space between the quotes, the formula field appears. Same thing, of course, if I try:

replace(replace({table.field},"a"," &quot," ","&quot

The field doesn't appear until I add a space between the final quotes, whether I use single or double quotes.

-LB

 

[Ngolem]

How about using the Ascii equivalent, chr(0), of a null instead of ""...see if that works

replace({table.field},"a",chr(0))


Jim Broadbent

 

[lbass]

Same result--the string disappears. If I make it chr(1), it reappears.

Okay, now here is a weird variation--I just got it to work if I tried replacing at least three letters in a string with "", but what it returned was not the entire string. Using the Xtreme database, and using {Product.ProductName} = "Roadster Mini Mtn Saddle" the following is returned:

Replaced Returned
"R" Null
"o" Null
"Ro" Null
"Roa" "ds"
"Road" "st"
"Roads" Null
"roadst" "er"
"r" Null
"er" Null
"ter" "Ro"
"ster" "Ro"
"dster" Null
"adster" "Ro"

In all of these tests, the "Mini Mtn Saddle" never appears either. But if I change the "" to " ", then it reappears partially--it only appears in its entirety and correctly if the number of spaces between the quotes is large enough. If I add more spaces one at a time, the entire field will appear. The number of spaces can be less than the number of letters replaced, but not by more than about two or three times less--I couldn't quite figure out the logic (if there is any!) underlying this.

-LB

 

[synapsevampire]

Have you applied the hotfixes?

This is a very strange thing, of course you might write a loop to iterate through the string, but the replace should work, I must have used it in CR 8...

-k

 

[kenhamady]

I remember this as a problme in v8. For me, it didn't go away until v8.5 but I don't think I ever applied any patches to v8.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lbass]

Thanks, Ken, for confirming that this is a problem with CR 8.0.

SV--I have only applied one patch, to correct a running total problem, although I think it fixed other things, too. A search of Crystal Decisions didn't turn up any mention of this problem that I could find, so I wonder if there is a patch, or whether I'd have to upgrade.

This is not a critical function for me--I just keep seeing the way people are using it in various threads, and it has never worked like that for me, so was curious why.

Thanks for clarifying this for me!

-LB