• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1579
  • Last Modified:

Convert Address to Block Numbers

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
Dragonnns
Asked:
Dragonnns
  • 5
  • 4
1 Solution
 
mlmccCommented:
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
 
DragonnnsAuthor Commented:
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
 
DragonnnsAuthor Commented:
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.

 
mlmccCommented:
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
 
DragonnnsAuthor Commented:
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
 
mlmccCommented:
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
 
DragonnnsAuthor Commented:
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
 
mlmccCommented:
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
 
DragonnnsAuthor Commented:
Now that works. :)

Thanks!
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now