Remove blank character

[lawre016]

Hi, I have a text field that may or may not start with a blank character. I am trying to select records where {SHOP_ORDERS.LABEL NAME ITEM} = {Labels.Name}, but if the first field has this blank in the front, it will not match with a record from the second table (no blanks in the second table). How can I select the equal records when they 'almost' match?
Tahnks!

 

[synapsevampire]

Consider posting technical information, such as Crystal version and database used.

The quick cheat is to use trim() on the field with the space being joined, but it's dependent upon the above, and I don't want to go through describing how you might address this in different versions and different databases, so please post specifics.

A global solution would be to create a View to trim out spaces.

-k

 

[lawre016]

I have Crystal 8.5 using SQL 7.0. The first field in the formula will either be " 123456" or "123456" and the second field is always "123456". Can I do the trim function on an "if first character is blank space" then trim it?
Thanks.

 

[Ngolem]

Just use

Trim({Table.stringfield})

IF there is a blank character at either end it will be removed...if no blank character exists then no harm done.

So a formula for comparison would look like

if trim({Table.probString}) = {Table.NoProbString} then
"something"
else
"somethingelse";

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lyanch]

You won't be able to do this in Crystal itself (with 8.5). You can't change the join SQL and can't join to a SQL expression.

You will either need to create a view that creates the correct link or a stored procedure that does the same.

Lisa

 

[synapsevampire]

One cheat is to use an ADO connection in CR 8.5, which would allow you to paste in your own SQL, otherwise Lisa described this very well.

-k