EXTRACT DATA FROM FIELDS
EXTRACT DATA FROM FIELDS
(OP)
I HAVE A FIELD CALLED ADDRESS. THIS ADDRESS FIELD HAS CITY, STATE AND ZIP. THIS FIELD IS SEPARATED BY SPACES. WHAT I NEED TO DO IS EXTRACT THE STATE WHICH IS IN THE MIDDLE OF THE FIELD. IT IS A 2 POSITION STATE CODE.
PLEASE HELP!!!!
PLEASE HELP!!!!





RE: EXTRACT DATA FROM FIELDS
You don't say what program you are getting the data from.
However lets say you have the data in a string called
Address$ and Address$="Long Beach CA 1234".
There are a number of ways to extract the state address, some more difficult than others.
Because the length of the city is not known the start position of the state code is also not known. You can also not search for the preceeding space because in the example shown Long Beach has a preceeding space before the word Beach.
However if the zip code must be entered and its length is fixed then this can be used.
State$=mid$(Address$,len (address$)-(ZL+3),2)
where ZL is the zip code length and the 3 because thats the precceding space before the zip code + the two character state code.
But like I said you need to make sure the zip code is entered and the code is the correct length.
A more elaborate method is the following
First dimension an array with all the state codes
Dim States(No of States) as string
Then define each element of the array with the state code
Statec(1) = "NY"
statec(2) = "CA"
etc
then have a for and next loop to search the string for the precence of the state code eg.
State$="No State Found"
StateN$="No State Found"
For temp = 1 to No of States
if instr(Address$," "+statec(temp)+" ") <> 0 then
State$=statec(temp)
StateN$=statename$(temp) 'See text Below
Endif
Next Temp
This searches the string for <SPACE>State Code<SPACE> for each of the states you have in your array. If no state is found then State$ equals "No State Found"
This method has the advantage of being independent of the state codes position in the string and the length of the string. and providing you have no 2 letter cities in the states it should work.
Another advantage is you coud have another array with the expaned names in it for the same postion eg
Statecode(2)="NY"
Statename(2)="New York"
That way after you have found the state you could see the full name of the state in the application you need to extract the data for.
I hope this makes sense to you.
If not and you have any problems then feel free to reply.
Regards
RE: EXTRACT DATA FROM FIELDS
Once you get this you can check that the last one is a correct representation of a ZIP (to ensure a ZIP indeed exists in the string: not necessary if you are sure of this), then take the preceding one, check for consistency with a state code (2 uppercode letters) and return it.
prex
motori@xcalcsREMOVE.com
http://www.xcalcs.com
Online tools for structural design
RE: EXTRACT DATA FROM FIELDS
Ref the previous post and the split function.
What if someone types in the zip code with a space in the middle?.
There would have to be some check to stop this other wise the precceding one item in the array would produce the first half of the zip code.
regards
RE: EXTRACT DATA FROM FIELDS
I'm in Australia and all our "zip" codes (called post codes here) are state orientated.
ie if it is in Victoria the first digit is 3xxx and so on. Does this hold true for US zip codes?? You could then, using laffalots neat finder then check against a portion of the zip code. (not many extra lines)
might not work but it's a thought.
regards
Don