Solved

Convert Address to Block Numbers

Posted on 2013-11-05
9
606 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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