Suppress records if first 5 characters are duplicated

Hi All,
I have a mailing list database that I was to suppress duplicates on if the first 5 characters of the ADDR field match the one in the previous (or any record!?) record ADDR field.

I can sort by ADDR easily enough, and 'Suppress if Duplicated', but it will not suppress the second entry if they are as follows
1 Alinda Road
1 Alinda Rd.

If I could just compare the first 5 or 6 characters of the field with a previous (or better yet, any record), and suppress (or highlight) then manual supression is a cinch.

Can ye help?
holdsworthbrosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Hello holdsworthbros,

You could create a group based on a formula field which extracts the first 5 characters of the address field.
Then put your data into the group header rather than the detail line. Suppress the detail line.
This will result in one record for each unique value of the first five characters.  Just the first record for each group will be shown.


Pete
0
Makman25Commented:
If you right click on the field you would like to suppress and go to the FORMAT FIELD item a popup will display. There is a check box that says suppress; don't check it, just follow it across to the X+2. Click on that and a formula box will pop up. Try writing a formula to do what you want.
0
Makman25Commented:
Use this in the Suppress Formula Popup using Crystal Syntax:

left({Address.Address},4)  = left(next({Address.Address}),4)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
What version of Crystal?  What type is the ADDR field?

If this is for CR8.5 or before and a memo field then the formulas will not work since memo fields aren't allowed in formulas.

You could change the SQL to retrieve the first 5-6 characters and sort on the field and then compare

mlmcc
0
mlmccCommented:
Glad i could help

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.