Solved

Crystal How to split 9-char zip

Posted on 2011-09-15
5
750 Views
Last Modified: 2012-05-12
I have a 9-character zip-code that I would like to be able to split into the format zipfirst5-ziplast4.
How do I code this in a Crystal formula?
Thanks
0
Comment
Question by:mbroad02
5 Comments
 
LVL 5

Expert Comment

by:wkrasner
ID: 36545703
totext(left(zip,5))+"-"+totext(right(zip,4))
0
 
LVL 5

Expert Comment

by:wkrasner
ID: 36545713
you might not need the totext if the zip is a string
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 36546511
The formula above will not work.  
If the zip field is a string you have to remove the ToText as mentioned.  If it is a number then you have to make sure there are no decimals and no thousand separators and it has to be converted before you use left and right

left(totext({zip},0,""),5) + "-" + right(totext({zip},0,"",4))


Another method.  If it is a string

Picture({ZipField},"xxxxx-xxxx")

If it is a number then you would need to add the leading 0's.

Picture(Right("000000000" & CStr({ZipField},0,""),9),"xxxxx-xxxx")


mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 36548150
If your zip code is a string, are all of them 9 digits, or do you have some 5 digit zip codes?  If you use Picture and some of your zip codes are only 5 digits, you'll get a "-" at the end.  You can avoid that by using:

if Length ({zip code field}) > 5 then
  Picture ({zip code field}, "xxxxx-xxxx")
else
  {zip code field}


 James
0
 

Author Closing Comment

by:mbroad02
ID: 36548745
Since the zip-9 is a string, the Picture({ZipField},"xxxxx-xxxx") formula worked perfectly.
Thank you!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sub Report Graphs In Main Report. 36 47
Next Full Week 4 76
"A string is required here" error in Formula 7 46
A string is required here error 20 11
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

28 Experts available now in Live!

Get 1:1 Help Now