| Author |
Message |
ulosawa
Newbie
Joined: 27 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 6
|
   Topic: Extract Postal code from customer address
Posted: 03 Mar 2008 at 6:59pm |
Hi Everybody,
I got another question which much alike to 'mid' function, but the problem is my data is not consistence in term of the data length. Due to to that i can't use the mid function.
My problem is how can i extract the postcode in the customer table which of course the postcode location will be different from one customer to another.
For examples
1) No 60-3-6 Seri Petaling, 57100 Kuala Lumpur.
2) No 416, Jalan Besar, 84800 Bukit Gambir, Muar, Johor
3) Lot 417 Lorong Mutiara, Taman Setia, 52500, Kuala Lumpur.
My Questions:
1) how can i extract the post code from the above sample??
2) how can I create a new line after the post code?? meaning that from the result it will show 2 lines first line for postcode and the second is city/location.
thanks   |
 IP Logged |
|
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 04 Mar 2008 at 12:17am |
|
This code is a bit tricky, but can be done. From what I gather, your zip code is always five numbers? If so, I would do a loop from the beginning to the end of the string. I would have a counter variable to track how many number appear in a row. Each time I hit a space character, reset the counter to zero. If the next character is a number, then increment it by one. If not, set it back to zero. Once I hit the next space, check if the counter is 5. If so, then you just found your zip code. Use the Mid() function to get the last five characters and put them in a variable. It's tricky, but it's certainly possible with some coding finesse.
To do a line break, insert a Chr(10) and Chr(13).
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
ulosawa
Newbie
Joined: 27 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 6
|
Posted: 04 Mar 2008 at 12:35am |
Hi Brian,
It's good to know it can be done. Yes, our customer postal code always in 5 numbers.
do you mind if you write in the coding script??
thanks & cheers
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 04 Mar 2008 at 1:03am |
sorry, but that's a bit much for me to do here. It will take some time to code and test.  You can work on it and when you have something I can review it and see if you are missing anything.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
ulosawa
Newbie
Joined: 27 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 6
|
Posted: 06 Mar 2008 at 3:19am |
 Actually i don't know how to do it.... it's a bit confusing  . But i'll try to get some help.
|
|
IP Logged |
|
ulosawa
Newbie
Joined: 27 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 6
|
Posted: 17 Mar 2008 at 12:02am |
Hi Brian / others,
How could i extract from the cparty.post_add data sample below??:
Cparty.post_add
Left({cparty.post_add},instr({cparty.post_add},"$")-2)result should get Level 56, Tower, but there is an error with this coding …..(seem it can’t read ‘instr’ command). When I run the
Len(instr({cparty.post_add},"$")-2) it give result ‘16’. When I put to the left/mid seem it can’t read the ‘instr’ command.
Any ideas??
|
|
IP Logged |
|
Savan
Senior Member
Joined: 14 Dec 2007
Location: India
Online Status: Offline
Posts: 162
|
Posted: 19 Mar 2008 at 12:24am |
Here's the code to extract the zip. what i did is checked for a number in the string, once i found it i checked for next 4 character if they are number (as zipcode is 5 digits). if all 5 character are number then i m assiging it to a variable and coming out of loop otherwise i m going for next charachter.
creat two formulas
1st formula name is "zipcode" which has following code
shared numbervar strlen := length({table.fieldname});
shared stringvar zipcode := "";
shared numbervar i := 0;
shared numbervar a := 0;
shared numbervar b := 0;
shared numbervar c := 0;
shared numbervar z := 0;
for i := 1 to strlen do
if mid({table.fieldname},i,1) in ["0","1","2","3","4","5","6","7","8","9"] then
(
a := i;
b := i;
c := i + 4;
z := 0;
zipcode := "";
for a := b to c do
( if mid({table.fieldname},a,1) in ["0","1","2","3","4","5","6","7","8","9"] then
(z := z + 1;
zipcode := zipcode &mid({table.fieldname},a,1);
if z = 5 then
i := i+strlen+1;
zipcode)
else
zipcode;
)
)
2. the 2nd formula is "zip" which has folllowing code
shared stringvar zipcode;
now keep both the formula in the report and supress zipcode.
You will get the zipcode
let me know, if this solves ur problem 
|
|
Thanks
Savan
|
|
IP Logged |
|
ulosawa
Newbie
Joined: 27 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 6
|
Posted: 24 Mar 2008 at 4:06am |
Hi Savan,
you are so great!! your coding did well exactly what i want   . one more thing, could you trace the problem of my coding here??
How could i extract from the cparty.post_add data sample below??:
Table name:- Cparty.post_add
Sample date: Level 56, Tower, $Petronas Twin Tower, *Kuala Lumpur City Center, Kuala Lumpur
My Coding:
Left({cparty.post_add},instr({cparty.post_add},"$")-2)result should get Level 56, Tower, but there is an error with this coding …..(seem it can’t read ‘instr’ command). When I run the
Len(instr({cparty.post_add},"$")-2) it give result ‘16’. When I put to the left/mid seem it can’t read the ‘instr’ command.
Any ideas??
Edited by ulosawa - 24 Mar 2008 at 4:10am
|
|
IP Logged |
|
Chance
Newbie
Joined: 10 Aug 2009
Location: United States
Online Status: Offline
Posts: 1
|
Posted: 10 Aug 2009 at 12:52pm |
|
Hello - I am trying to take the formula you provided below to extract a 7 digit file number from an alpha numeric string in Crystal 11. This worked well until I found that the formula is pulling the number from the previous record. Do you have any ideas of what I may need to tweak in order for this to pull from the correct record?
|
|
Thanks - Chance
|
|
IP Logged |
|
|
|
