Solved

Convert Address to Block Numbers

Posted on 2013-11-05
9
535 Views
Last Modified: 2013-11-07
Hello,

I am trying to take an address string and convert the specific address to a block number.

For example: 123 N Main Street
would become: 100 Block N Main Street

Since the address string may or may not start with a number (the address could be Main / First) and since that number could be anywhere between 1 and 6 characters long, I'm a bit stumped.

I can use an if/then/else and a numeric(left(fieldname,X)) statement to determine if it starts with a number but then I need to convert that number to blocks, strip the text it is replacing, add the word Block and then the rest of the field.

I swear I saw (and maybe even used) such a formula but I can't find it anywhere. Any help is appreciated.
0
Comment
Question by:Dragonnns
  • 5
  • 4
9 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39624996
You can use the VAL function to get the value of the address number

Val ("123 N Main Street ") = 123
Val(" N Main Street") = 0

What do you want 10 Main street to return?

Try this idea

If Val({StreetAddressFIeld}) > 0 then
    CStr(Val({StreetAddressFIeld}) \ 100 * 100,0,"") & Mid({StreetAddressFIeld}, InStr({StreetAddressField}," ")
Else
    {StreetAddressFIeld}

Another way would be

Local StringVar Array AddressParts;
AddressParts := Split({StreetAddressFIeld}, " ")
If Val(AddressParts[1] > 0 then
     AddressParts[1] := CStr(Val(AddressParts[1]) \ 100 * 100,0,"");
Join(AddressParts," ")

mlmcc
0
 

Author Comment

by:Dragonnns
ID: 39630120
The first formula converts 7002 Spaniel Road to 07002 Spaniel Road, 11802 Peoria Street to 111802 Peoria Street and 1105 Ohio Street to 01105 Ohio Street.

The second one I couldn't get to work.

For those under 100, I want to return 0 Block.
0
 

Author Comment

by:Dragonnns
ID: 39630189
This did get me on a path to make this work. This is what I ended up doing. There may be a simpler way but this works for now.

NumberVar Lengths  := If Val({StreetAddressField}) > 0 then Len(ToText(Val({StreetAddressField}))) + 1 ;

StringVar Address := Right({StreetAddressField}, len({StreetAddressField})-Lengths +1) ;

If {StreetAddressField} StartsWith ToText(Val({StreetAddressField}),0,'') + 'ST' then {StreetAddressField} else
If {StreetAddressField} StartsWith ToText(Val({StreetAddressField}),0,'') + 'ND' then {StreetAddressField} else
If {StreetAddressField} StartsWith ToText(Val({StreetAddressField}),0,'') + 'RD' then {StreetAddressField} else
If {StreetAddressField} StartsWith ToText(Val({StreetAddressField}),0,'') + 'TH' then {StreetAddressField} else
If Val({StreetAddressField}) > 0 then
 CStr((Truncate(Val({StreetAddressField})/100)*100),0,"") + ' BLOCK ' + Address
else
 {StreetAddressField} ;
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 100

Expert Comment

by:mlmcc
ID: 39630263
Try this one

If Val({StreetAddressFIeld}) > 0 then
    CStr((Val({StreetAddressFIeld}) \ 100) * 100,0,"") & Mid({StreetAddressFIeld}, InStr({StreetAddressField}," "))
Else
    {StreetAddressFIeld}

It seems Crystal is doing the division incorrectly.  It seems the 100 * 100 was done first then the division.

The other one should be
Local StringVar Array AddressParts;

AddressParts := Split({StreetAddressFIeld}, " ");
If Val(AddressParts[1]) > 0 then
     AddressParts[1] := CStr((Val(AddressParts[1]) \ 100) * 100,0,"");
Join(AddressParts," ")

mlmcc
0
 

Author Comment

by:Dragonnns
ID: 39630289
The LocalArray works better than your first formula but neither address addresses such as 5th and Main (which exist in this database).

Though not elegant, the formula I posted works. I wouldn't have gotten there without your help though!

Thanks!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39630584
I had assumed the address would always start with a number then street or just the street.  Didn't think about 5th and Main

Try this

Local StringVar Array AddressParts;

AddressParts := Split({StreetAddressFIeld}, " ");
If
If IsNumeric(AddressParts[1]) > 0 then
     AddressParts[1] := CStr((Val(AddressParts[1]) \ 100) * 100,0,"");
Join(AddressParts," ")

mlmcc
0
 

Author Comment

by:Dragonnns
ID: 39630680
I get a "The keyword 'then' is missing" error message with your formula. If I remove the duplicate "If", I get a "A number, currency amount, date, time, date-time, or string is required here." and places my cursor on the "IsNumeric(AddressParts[1])" field.

Thoughts?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39630725
Sorry.  That's what I I get for editing and not trying it.

Local StringVar Array AddressParts;

AddressParts := Split({StreetAddressFIeld}, " ");
If IsNumeric(AddressParts[1])  then
     AddressParts[1] := CStr((Val(AddressParts[1]) \ 100) * 100,0,"");
Join(AddressParts," ")

Open in new window


mlmcc
0
 

Author Comment

by:Dragonnns
ID: 39630774
Now that works. :)

Thanks!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

831 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