Help!!

cindy011680 · Jan 12, 2004

Anyone,

Can someone double check this formula to make everything looks ok. This is for trying to get the address field to not show in all uppercase letters.(I have Crystal 8.)

Thank you

stringvar tempstring;
stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,27)) &
right(tempstring, len(tempstring) - 27);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring;
stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,26)) &
right(tempstring, len(tempstring) - 26);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring;
stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,25)) &
right(tempstring, len(tempstring) - 25);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,24)) &
right(tempstring, len(tempstring) - 24);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,23)) &
right(tempstring, len(tempstring) - 23);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,22)) &
right(tempstring, len(tempstring) - 22);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,21)) &
right(tempstring, len(tempstring) - 21);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring;
stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,27)) &
right(tempstring, len(tempstring) - 27);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring;
stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,26)) &
right(tempstring, len(tempstring) - 26);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring;
stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,25)) &
right(tempstring, len(tempstring) - 25);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,24)) &
right(tempstring, len(tempstring) - 24);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,23)) &
right(tempstring, len(tempstring) - 23);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,22)) &
right(tempstring, len(tempstring) - 22);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

stringvar tempstring2;
tempstring:= " ";
tempstring2:= " ";
tempstring:=right

({v_WidSupplements.LocalAddress}, 250);
tempstring := lcase(left (tempstring,21)) &
right(tempstring, len(tempstring) - 21);
while instr(tempstring, " " ) <> 0 do
(tempstring2 := tempstring2 & left
(tempstring, instr(tempstring, " " ));
tempstring := right (tempstring, len
(tempstring) - instr(tempstring, " " ));
tempstring := ucase(left (tempstring, 1)) &
right(tempstring, len(tempstring) - 1);
);
tempstring2 := tempstring2 & tempstring;

dgillz · Jan 12, 2004

What do you want to do, show this in Proper Case (Where the first letter of every word is capitalized)?

If so, save yourself some time and download the ProperCase() function from the crystal decisions website.

If you want to do something else, please explain what it is you want to do.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

cindy011680 · Jan 12, 2004

This is exactly what I am trying to do but, when have strict rules about downloading material.That is why I am having write out the full formula.If you know of any formula to use to break through this parameter, please let me know.

Thanks

Reebo99 · Jan 12, 2004

The following formula will make the first character after a space Upper Case, with the rest in lowercase.

Numbervar Counter := 0;
StringVar Address := {v_WidSupplements.LocalAddress};
StringVar Result;
While Counter <> len(Address) Do
(
Counter := Counter + 1;
If Counter = 1 or Mid(Address,Counter-1,1) = " " then
Result := Result&UCase(Mid(Address,Counter,1)) else
Result := Result&LCase(Mid(Address,Counter,1))
);
Result;

Reebo
UK

"Before you judge a man, walk a mile in his shoes. After that, who cares? ...He's a mile away and you've got his shoes." - Billy Connolly

lyanch · Jan 12, 2004

You don't mention which database you use, but the proper case type function is available in several databases. Using aa available SQL expression is definitely easier that rewriting this functionality.

Lisa

cindy011680 · Jan 12, 2004

Reebo,

When I use that formula its is giving a error message , saying that the string needs to be a length of 250. What do I then?

synapsevampire · Jan 12, 2004

In my original post I had suggested that you use a loop, as in Reebo's example.

I'm not aware of an error that states anything about a string being 250, though there is an error stating that a formula cannot exceed 254 in CR 8.5 or less, perhaps that's what you mean?

If so, try posting the actual error and what you tried, and state the length of your field (right click it and select browse data to learn this).

Having coded mixed case functions numerous times in the past for addresses, you might want to add to this the ability to anayze at the word level, rather than each character. One reason why this is important is the PO Box 1T33

The above will produce Po Box 1t33, which looks bad.

The theory is that you separate each word (check each starting and ending point of each word by looking for the next space) in the address, make sure that it does NOT contain key abbreviations by checking for PO APO FPO, etc., and that there aren't any dashes or periods in each word, if not, then loop through that word as in Reebos example.

It's not too bad, and should prove reusable across multiple reports.

If I find time I'll code this up and create a FAQ out of it in the next week as it's a fairly common request.

It will probably take an hour or two to do it fairly well, but once done, it should prove much more useful than the UCASE function.

-k

cindy011680 · Jan 12, 2004

Reebo,

I am sorry, the error is stating that a formula cannot exceed 254 in CR 8.5 or less.

Thank you for you help thus far!

dgillz · Jan 13, 2004

It is not the formula that cannot exceed 254, it is the output of the formula that cannot exceed 254.

I would suggest that you are no resetting your variables t '' somewhere along the line.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

Reebo99 · Jan 14, 2004

This formula will assume anything with a number in should be Upper Case.

StringVar Address := LCase({Table1.Address});
NumberVar Count1 := 0;
StringVar Word;
StringVar WordResult;
StringVar EndResult;

StringVar Array Words := Split(Address," &quot

;
While Count1 <> UBound(Words) Do
(
Count1 := Count1 + 1;
Word := Words[Count1];
NumberVar Count2 := 0;
While Count2 <> Len(Word) Do
(
Count2 := Count2 + 1;
If Count2 = 1 then
WordResult := WordResult&UCase(Mid(Word,Count2,1)) else
WordResult := WordResult&Mid(Word,Count2,1);
If ISNumeric(Mid(Word,Count2,1)) then
(
WordResult := UCase(Word);
Exit While
);
);
If Count1 <> UBound(Words) then WordResult := WordResult&" ";
EndResult := EndResult&WordResult;
WordResult := "";
);
EndResult;

Replace the item in bold with your field name.
You can add many more assumptions for UCASE by adding them within the line If ISNumeric(Mid(Word,Count2,1)) then
e.g.
If ISNumeric(Mid(Word,Count2,1)) or Mid(Word,Count2,1)="-" then etc. etc.

Reebo
UK

"Before you judge a man, walk a mile in his shoes. After that, who cares? ...He's a mile away and you've got his shoes." - Billy Connolly

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!

Help!!

cindy011680

Technical User

dgillz

Instructor

cindy011680

Technical User

Reebo99

MIS

lyanch

Programmer

cindy011680

Technical User

synapsevampire

Programmer

cindy011680

Technical User

dgillz

Instructor

Reebo99

MIS

Similar threads

Part and Inventory Search

Sponsor