Proper use of Left(.) and right(.) 1

[btcomput]

Hi,

I'm trying to develop a recordselection formula in a VB program to search for all records with the string:

Fred's Bass Club

in a field called "club" within a table called "results"

The problem is that in this case, the string has an apostrophe in it.
I have tried the following code:

If InStr(1, cmbclub.Text, "'&quot > 0 Then
inpos = InStr(1, cmbclub.Text, "'&quot
fmlatext = " left('" & inpos - 1 & "',{result.club}) = '" + Left(cmbclub.Text, inpos - 1) + "' and right('" & (Len(cmbclub.Text)) - inpos & "',{result.club}) = '" + Right(cmbclub.Text, (Len(cmbclub.Text)) - inpos) + "'"
Else
fmlatext = " {result.club} = '" & cmbclub.Text & "'"
End If

Which produces:
left('4',{result.club}) = 'Fred' and right('11',{result.club}) = 's Bass Club'"

This produces the error: "error in formula".

Any help would be greatly appreciated.

Thanks.

 

[kenhamady]

The final formula you want to pass is either:

"Fred's Bass Club" in {result.club}

or this one:

{result.club} like "*Fred's Bass Club*"


Either of these would do what you want if you can pass it in this form. Are you having trouble getting it to work in Crystal, passing it to Crystal or building it in VB.
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[btcomput]

Thanks Ken,

I am wrinting the code within a VB program, and using the code: report1.SelectionFormula = fmlatext
to send it to CR.

The code displayed in my original post works fine if the string does not include an apostrophe, otherwise I get "formula error" messages.

Elsewhere whithin VB I have had to use Left and Right to search for all characters to the left and right of the ' in order avoid errors. I expected CR to be the same.

 

[kenhamady]

Did you solve the problem?
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[btcomput]

Ken,

I don't have it working yet.

The following code:
fmlatext = "'" & cmbclub.Text & "' in {result.club}"

produces:
"'Fred's Bass Club' in {result.club}"

and looks like it should work, but doesn't. It DOES work if the string does not contain an apostrophe, such as: "Big Fish Club".

How do I write the code to deal with the apostrophe??
Both my original code, posted in my first message, and the code listed here look like they should work.

I can't help thinking that searching for a match for the text to the left of the apostrophe, "And" ... the text to the right of the apostrophe, ie:

left('4',{result.club}) = 'Fred' and right('11',{result.club}) = 's Bass Club'"

is the way to go. That process works for sql queries elsewhere in the program.

Any help would be appreciated.

Thanks

 

[kenhamady]

You are using single quotes to surround the literal name in the Crystal formula, and this won't work if there is a single quote in the literal. You need to pass double quotes in this instance to Crystal so that it doesn't get confused by the single quote.

Can you pass the literal to Crystal with double quotes, or will this present problems to you in your VB coding?

If you need to ask CR to locate the position of a Character you would need to use the InStr function. InStr is a CR function which returns the numeric position of a character string within a larger string.

Left and Right in CR allow you to extract characters from the right and left end of the string, but you have to know or calculate how many to take off using InStr. The correct syntax in CR is :

Left ( {field} , number )

i.e. Left ( {table.name} , 5 )

Crystal also has a substring operator which is the Square Bracket:

{field} [ 3 to 6] would return all characters from the string from 3rd to 6 position. The numbers can be expressions or numeric fields. When selecting, this is usually processed faster than using the Left function. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[flaubert]

Hello,

Firstly, I am not sure what you mean by the following:

left('4',{result.club}) = 'Fred' and right('11',{result.club}) = 's Bass Club'"

The syntax of left is left(string,chars) which will return the left 'chars' characters of 'string'.

Anyway, I think this is all beside the point.

In VB, if you want to print a string like,

Paul said, "Hello"

then you would type

msgbox "Paul said, ""Hello""".

ie. for every quote you want to print, you enter it twice in the string. This trick seems to work in most languages, and in Crystal, which uses single quotes to delimit text rather than double (as in VB) this trick transports to single quotes.

The upshot of all this is that I think the formula you need is simply,

{result.club} = 'Fred''s Bass Club'

This will return true if {result.club} contains

Fred's Bass Club

and is hopefully a lot less complicated.

 

[btcomput]

What this all boils down to is that I do not know, in advance, what string the user will be searching for.
The program reads records from the user's database and plugs a list of clubs into a drop-down combo box, "cmbclub".
Some of those club names MAY contain an apostrophe, most will not.
I have no difficulty at all building code that will successfully search for a user-selected club if the club name does NOT contain a pesky apostrophe.

The following code:
fmlatext = "{result.club} = '" & cmbclub.Text & "'"
report1.SelectionFormula = fmlatext

Works fine in this case.

If the club selected in the cmbclub combo box happens to contain an apostrophe, this code fails.

How do I modify this code so that if the user selects a club name that just happens to have an apostrophe, the search works?

 

[flaubert]

You could modify your code as follows

fmlatext = "{result.club} = '" & Replace(cmbclub.Text,"'","''&quot & "'"
report1.SelectionFormula = fmlatext

The Replace function is part of the VBA library if you are using VB6. The above will replace any instances of 'single' single quotes, and replace them with 'double' single quotes!

If you get my meaning...

 

[kenhamady]

If you can build eithr of the the formulas I suggested in VB and pass it in that form to CR then it won't matter if there are single quotes or not. It will work either way.

It will only fail if there is a double quote in the string, but these are pretty rare in this type of data. Crystal can delimit strings with either single or double quotes. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[btcomput]

I think I finally have it working!

The code I ended up with is based on the {field} [ 3 to 6] format. The code isn't pretty, but it works.

Basicaly the code works this way:

If InStr(1, cmbclub.Text, "'&quot > 0 Then
spot = InStr(1, cmbclub.Text, "'&quot
clublen = len(cmbclub.text)
fmlatext = Left(cmbclub.Text, spot - 1) & "' in {result.club} [1 to " & Str(spot - 1) & "]"
fmlatext = fmlatext + " and '" & Right(cmbclub.Text, clublen - spot ) & "' in {result.club} [" & Str(spot + 1) & " to " & Str(clublen) & "]"

else
fmlatext = "{result.club} = '" & cmbclub.Text & "'"
end if

If an apostrophe exists the code produces a string such as:
" 'Bubba' in {result.club} [1 to 5] and 's Raiders' in {result.club} [7 to 15]"

Thanks for all the help.

 

[flaubert]

Just out of interest, did you try the Replace solution?
ie. instead of using:

If InStr(1, cmbclub.Text, "'&quot > 0 Then
spot = InStr(1, cmbclub.Text, "'&quot
clublen = len(cmbclub.text)
fmlatext = Left(cmbclub.Text, spot - 1) & "' in {result.club} [1 to " & Str(spot - 1) & "]"
fmlatext = fmlatext + " and '" & Right(cmbclub.Text, clublen - spot ) & "' in {result.club} [" & Str(spot + 1) & " to " & Str(clublen) & "]"

else
fmlatext = "{result.club} = '" & cmbclub.Text & "'"
end if

you could use:

fmlatext = "{result.club} = '" & Replace(cmbclub.Text,"'","''&quot & "'"
report1.SelectionFormula = fmlatext

or even just:

report1.SelectionFormula = "{result.club} = '" & Replace(cmbclub.Text,"'","''&quot & "'"

This seems a "prettier" solution. Instead of producing the formula:

" 'Bubba' in {result.club} [1 to 5] and 's Raiders' in {result.club} [7 to 15]"

this will produce

"{result.club} = 'Bubba''s Raiders'"

which WILL work (if you don't believe me, try it).

The only reason I ask is that while your solution is fine for names which contain only one apostrophe, it will fail for names like "Bubba's and Jim's Raiders", which contain two, or even the horrific "Bert 'n' Ernie's". However unlikely you think this is, it is a scalability issue, and you need to decide whether you're going to worry about it or not. (It is also a user issue. If you're not the one putting the data in, who knows what some unscrupulous twitchy-fingered end user might decide to throw at your report? Particularly evil users might even just type a single quote in the field and leave it at that.)

Your formula will also fail for clubs that have the apostrophe right at the beginning or the end. Contrived examples like "Singin' 'n' Dancin'" or "'Ungry Joe's" might make you think it's not worth the bother. You might also not be bothered by the fact that 'in' clauses are slow compared to straight equivalences. It's up to you. Having faced all these problems myself in the past, I know how annoying it can be when the Neapolitan Mafia moves in next door, opens up their new club just around the corner, and calls it "'Ndrangheta".

 

[btcomput]

Hi,

I haven't upgraded yet to VB6, so that's the reason for not using Replace. Thanks for letting me know about it though.

Next, I have realized all along that my code would not handle multiple occurances of the apostrophe. I've looked at a couple users' databases, and apostrophes occur rarely, but you are right, it could present a problem. Creating code to deal with this sounds like more work than it's worth. VB6's Replace sounds much more simple.

I've also considered changing the program and/or the underlying database structure so that I don't search for the club name at all, but search for an id number of some kind. I can still make it look to the user as though they are searching by name.

You have made some good points and I appreciate the help.

Thanks