Rounding off last 2 digits of an address in a mixed field 1

[PDAnalyst]

Hi,

I am using CR9 with an MS Access based geodatabase.

I have to create a report that will round of the last 2 digits of an address. The field also contains streets that starts with a number. I need to leave these ones alone and only round the street address numbers and add "Block of" to the final address. All of the address numbers are followed by a space.

Any help is appreciated; Here is how the data looks:

1234 Main St.
04 Central Ave.
1st St/Main St.
7th St./Lincoln


I need this to show up like this:
1200 Block of Main St.
00 Block of Central Ave.
1st St/Main St.
7th St./Lincoln

Thanks

S.Egilmez

 

[lbass]

I'm guessing that you want the address number rounded down, so that 257 Central Street becomes "Block of 200 Central Street". Try the following:

"Block of " +
if isnumeric(split({table.address}," ")[1]) then
(
if val(split({table.address}," ")[1]) < 100 then
totext(int(val(split({table.address}," ")[1])/100)*100,"00") else
totext(int(val(split({table.address}," ")[1])/100)*100,0,"")
) +" "+ mid({table.address}, instr({table.address}," ")+1) else
{table.address};

-LB

 

[PDAnalyst]

Thanks lbass,


The rounding portion now works, the only problem is that now all of the streets have "Block of" at the beginning. I have to devise a way to seperate the intersection streets and make sure they do not receive the "Block of".
Since all of the intersections are supposed to have the "/" in them, do you think I can write a second if-then-else statement that looks for the slash and only round the ones that it did not encounter slash to be rounded down? (since I have numbered streets, I cannot use the starts with numeric as a select option)

S. Egilmez

 

[lbass]

Try:

if isnumeric(split({table.address}," ")[1]) and
instr({table.address},"/") = 0 then "Block of " +
(
if val(split({table.address}," ")[1]) < 100 then
totext(int(val(split({table.address}," ")[1])/100)*100,"00") else
totext(int(val(split({table.address}," ")[1])/100)*100,0,"")
) +" "+ mid({table.address}, instr({table.address}," ")+1) else
{table.address};

-LB

 

[PDAnalyst]

Thanks lbass,

IT WORKED,

I appreciate all of your help. You saved me again,

S.Egilmez

 

[PDAnalyst]

Lbass,

if you would like to see the finished product follow the link.

Again, thanks for all of your help,

S.Egilmez

http://santamonicapd.org/Press_Info/DailyReports/cfs012606.pdf

 

[lbass]

Thanks--it's nice to see how suggestions fit into a finished product.

-LB

 

[socalvelo]

I have a very similar issue to PDAnalysist and have to prepare a report that also replaces the last two numbers as zeros for confidentiality purposes. I also need to get rid of apartment numbers, they appear with a # and numbers.

1234 S MAIN ST #45
14566 W 54TH ST #A122

I've been able to remove the "#" with the replace command, but I can't figure our how to get rid of the numbers also since they appear in different positions for each record.

Any help would be much appreciated

 

[lbass]

Try:

stringvar addr :=
if isnumeric(split({table.address}," ")[1]) and
instr({table.address},"/") = 0 then "Block of " +
(
if val(split({table.address}," ")[1]) < 100 then
totext(int(val(split({table.address}," ")[1])/100)*100,"00") else
totext(int(val(split({table.address}," ")[1])/100)*100,0,"")
) +" "+ mid({table.address}, instr({table.address}," ")+1) else
{table.address};
if instr(addr,"#") > 0 then
trim(left(addr,instr(addr,"#")-1)) else
addr

-LB

 

[socalvelo]

LB,

Thank you so much. It works perfect. You saved me a lot of time. I couldn't figure out how to use the trim command that way.

Ed