Solved

Convert Address to Block Numbers

Posted on 2013-11-05
9
505 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Dragonnns
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Now that works. :)

Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now