Convert Abbreviations

[bhansen]

Is there a way to convert abbreviations in my data such as;

" Agar Ave " to " Agar Avenue"

Very new to Crystal so it might be easy.

Thanks

 

[mbarron]

I don't believe there is a built in function to do this, however it is possible. It could end up being quite a large formula depending upon how many abbreviations you want to chek for and convert.

The following formula will look for "Ave" or "St" in a field and replace them with "Avenue" or "Street". If the abbreviations aren't found the field itself will be displayed.


If "Ave" in {address.field} then replace({address.field},"Ave","Avenue&quot else
If "St" in {address.field} then replace({address.field},"St","Street&quot else
{address.field}
Mike

 

[dgillz]

To do a really good job of what Mike is suggesting, you would need to store the text, including whatever is replaced, as a variable through each step of the nested
if-then-else logic. This way you could replace more than one abbreviation per address. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[bhansen]

I tried the following but received unexpected results

if "AVE" in {CUSTOMER MAINTENANCE.STREET NAME} then replace ({CUSTOMER MAINTENANCE.STREET NAME},"AVE","AVENUE&quot else {CUSTOMER MAINTENANCE.STREET NAME}

"Agar Avenue" in the reports appears as " Agar Avenuenue"

the problem is my data some appear as ave and some as avenue.

Dgillz a short example of your suggestion would be great.

Thanks

 

[dgillz]

"Agar Avenue" in the reports appears as " Agar Avenuenue"

This is because the formula is replaceing "ave" in the word "avenue", which does not need any text replacement to be done. You will need to test for the full correct string first, then if that test fails, test for the abbreviation.

The following shoes how to do this and the variable technique I suggested.

StingVar Address:="";

If "avenue" in {Streetname) then Address:={streetname} else
if "ave" in Address then Address:=Replace(Address,"ave","avenue" else
If "Boulevard" in Address then Address; else
If "Blvd" in Address then Address:=Replace(Address,"blvd",boulevard"

Address;

I may have a syntax error or two here but I think you get the idea. If you have problems post and we will work it out.
Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[bhansen]

Dgillz

That seems to work except if you have an address such as

" Hwy 16 West " you get " Hwy 16 Westreet "

Is there any way to make it so when it checks for maybe say spaces on each side of the word?

Thanks Again

 

[Ngolem]

here try this though it may not be perfect...

Your test can be " ave " or " st "

BUT! you have another dilema if you have mixed case in your addresses
(eg. Ave or ave or even perhaps Ave. or ave.)

You would have to address those cases as well perhaps

 

[bhansen]

I tried the " ave " but my data doesn't have a space after it, this all works great except for 1 instance I have

" Hwy 16 W" which I want " Hwy 16 West "

I believe if I try anything with the " W " it will change words starting with W.

I think I need some way to say if data has a space then a value and nothing after do this.

God this is fun

 

[Roadkill150]

Sounds like the users need to have a standardized entry method! There may be hundreds of variations that could include various abbreviations and misspellings. Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com

 

[bhansen]

That's the whole problem, we have years of non standardized entry that we have to generate reports off of.

 

[Ngolem]

I don't think there is an answer to your problem.....there will always be one-more-exception. Database repair/standardization is the only permanent solution.

 

[dgillz]

Yes, I would start writing some update queries to change the values in the database itself. After that database cleanup is done, modify your crystal report to look at new and changed addresses from the previous day. Throw the user's name on the report if possible.

Schedule the report to run nightly if you have Crystal Info or Crystal Enterprise. Every time somebody enters "Ave" or "Blvd", beat them over the head with this report, figuratively. Eventually you will train your users not to do this.

Training users is a little like painting the Golden Gate bridge for a living. As soon as you are done painting it, you hop in a truck, drive back to the other side of the bridge, and start painting again. Its a never ending job.

Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[bladergames]

Hire a bunch of temps.. Export the data to excel and start doing replaces with certain configurations.. In excel, you can do replaces on whole words, such as "W" so you don't update all W's with the word west. Bite the bullet. That way using excel will get alot of people that can understand.. then import it all back in and start over with good error checking. Don't mess with anything in CR for that stuff.