Suppress records if first 5 characters are duplicated

Posted on 2004-11-25
Last Modified: 2008-05-28
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?
Question by:holdsworthbros
    LVL 77

    Assisted Solution

    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.


    Expert Comment

    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.

    Accepted Solution

    Use this in the Suppress Formula Popup using Crystal Syntax:

    left({Address.Address},4)  = left(next({Address.Address}),4)
    LVL 100

    Assisted Solution

    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

    LVL 100

    Expert Comment

    Glad i could help


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now