Splitting Address fields

[crzycrystlgrl]

ok, happy sunday, I am trying to figure out a way to split an address field into four fields.
I am using CR9.

ex.
16543 15 MILE RD STE T4
1500 MARKER AVE #1000
211 EAST RD

I need to be able to have the first line in the example to return

16453
15 MILE
RD
STE T4

the second one as
1500
MARKER
AVE
#1000

etc...
I've gotten as far as Val (addressfield)
to give me the street address but hit a snag when the road number begins with a number

Any help would be greatly appreaciated
thanks
-Cynthia

 

[dgillz]

What are the rules for splitting the field?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.srhconsulting.com

 

[crzycrystlgrl]

That is the problem. I cannot seem to find a common rule for all the variables. The only thing I can come up with is the first part of the field is a number (address), then a space (the name of the street), a space (whether it is a road or ave or blvd) and then a suite if applicable comes last....
All the character counts a variable as #s and road names are different lengths...
I am stuck.

 

[Turkbear]

HI,
With no consistant rule for the field's contents, you would need to do an extreme amount of if/then/else testing to get what you want.

If that kind of thing is really needed, you may need to create a SP and pre-process the data in the database.
(even then, be sure the need is worth the effort)

 

[crzycrystlgrl]

Might you know of some if then else statement which would say "if there was a space...then...." ?
thanks

 

[Turkbear]

Hi,
Look into using the Instr function to find spaces - But, in your situation, you may need to use a loop to check for multiple spaces and get their positions, so you will know where you want to split ( then again, with no consistant format, how will you know that?)