Forumula Help (ZipCodes)

[danzing]

Hi all I am trying to do something like this, I have a database field that is zip codes I want to separate zip codes by state I have a text list like this already done




IF (ZIP GE 01000 AND ZIP LE 02699) ZIPC=1.
IF (ZIP GE 02700 AND ZIP LE 02999) ZIPC=2.

IF (ZIP GE 03000 AND ZIP LE 03499) ZIPC=3.
IF (ZIP GE 03800 AND ZIP LE 03999) ZIPC=3.

and at the end of the text lisrt is this

VALUE LABELS

ZIPC 1'MA' 2'RI' 3'NH' 4'VT' 6'ME' 7'CT' 8'NJ' 9'NY' 10'PA'
11'DE'
12'DC' 13'MD' 14'VA' 15'WV' 16'NC' 17'SC' 18'GA' 19'FL' 20'AL' 21'MS' 22'TN'
23'KY' 24'OH' 25'IN' 26'MI' 27'IA' 28'WI' 29'MN' 30'SD' 31'ND' 32'MT' 33'IL'
34'MO' 35'KS' 36'NE' 37'LA' 38'AR' 39'OK' 40'TX' 41'CO' 42'WY' 43'ID' 44'UT'
45'AZ' 46'NM' 47'NV' 48'CA-SO' 49'CA-NO' 50'HI' 51'OR' 52'WA' 53'AK'
54'CANADA'.

And so on,

what I am trying to do is come up with a formula to make this check my zip code field and bring it back by the state it is in

I do not know how to do this I was thinking their has to be a way that I can use these existing text files and come up with some kind of formula any and all help would be fantastic

Thanks in advance

http://www.whiteyman.com

 

[rosemaryl]

Dear Danzing,

The most sensible thing to do is to store the state information in the database. Barring that possibility then you can do what you want.

First, in your array of States, remove the numbers and create an array variable to hold the data. You don't need the numbers because an element's position in the array is numerical.

//begin formula
stringvar array ST;
ST := makearray('MA','RI','NH','VT','','ME','CT','NJ','NY','PA','');

//finish filling the above array. By the way, number 5 was
//missing in your example list.
//Also, at the end in element 55 make a blank value (as I
//show above in element 11) so
//that you can use that as a default if a zip doesn't meet
//any of the criteria

//In same formula, continue below:

Select Tonumber({Clients.Zip}) //replace with your zip field
//my zip was a string so I had to convert to a number!
//take off the ToNumber if yours is not a string!

case 01000 to 02699 : ST[1]
case 02700 to 02999 : ST[2]
case 03000 to 03499 : ST[3]
case 03800 to 03999 : ST[4]
//finsih populating the case statment through 54
default : ST[11]

//change 11 above to 55 in actual formula if you follow
//my advice to have a blank last element.

This should work...let me know if you need assistance.

Ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[danzing]

How awesome it worked ,

you are the bomb (Means really good)

http://www.whiteyman.com