Moving the last numbers of a field

[Dimitris]

Hello, I have a table called ALL. In this table there is a field called
ADR09 and a field called NOUMERO. The field ADR09 has many entries that end
with a number. I need those numbers deleted from the ADR09 field and entered
in the NOUMERO field. So if the ADR09 field has for example Adams 44 entered
I need the word "Adams" to remain there and the number "44" to be be cut off
and moved to the NOUMERO field.

But note that there may be numbers in other entried at other positions I
just need the ones that are in the END to be cut off and moved. Also not all
entries have numbers. The ones that do have numbers at the end, may have 1,2
,3 or any number of digits at the end. Need all those digits to be moved. So
I guess we need to find a way to see which entries have a number after the
last space and cut and move those numbers.

Can someone please help?

Thanks
Dimitris

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE ALL
SET Noumero = Mid(Adr09,InstrRev(Adr09," ")+1)
, ADR09 = LEFT(Adr09,InstrRev(Adr09," ")-1)
WHERE IsNumeric(Mid(Adr09,InstrRev(Adr09," ")-1))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale_Fye via AccessMonster.com]

John made the assumption (based on your one example) that all the records
which have a space and where the space is followed by a numeric value should
be edited. Is that a valid assumption? Will every record that needs to be
split have a space, followed by a numeric value of some unknown length?

If so, I think he meant that the WHERE clause should look at every thing to
the Right of the space, not the left. Then to account for the records that
don't have any spaces, you should also add a criteria to eliminate them
(otherwise, the MID function will generate an error). So, change:

WHERE IsNumeric(Mid(Adr09,InstrRev(Adr09," ")-1))

to:

WHERE instr([ADR09], " ") > 0
AND IsNumeric(Mid(Adr09,InstrRev(Adr09," ")+1))

HTH
Dale

 

[John Spencer]

A better test would have been
IsNumeric(Mid(Adr09,InstrRev(Adr09 & ""," ")+1))

There is really no need to test for the presence of the space unless an
address would consist of ONLY numbers, but I did need to add a zero-length
string to handle null values in ADR09 and avoid an error when calling InstrRev.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John made the assumption (based on your one example) that all the records
which have a space and where the space is followed by a numeric value should
be edited. Is that a valid assumption? Will every record that needs to be
split have a space, followed by a numeric value of some unknown length?

If so, I think he meant that the WHERE clause should look at every thing to
the Right of the space, not the left. Then to account for the records that
don't have any spaces, you should also add a criteria to eliminate them
(otherwise, the MID function will generate an error). So, change:

WHERE IsNumeric(Mid(Adr09,InstrRev(Adr09," ")-1))

to:

WHERE instr([ADR09], " ") > 0
AND IsNumeric(Mid(Adr09,InstrRev(Adr09," ")+1))

HTH
Dale

Hello, I have a table called ALL. In this table there is a field called
ADR09 and a field called NOUMERO. The field ADR09 has many entries that end
with a number. I need those numbers deleted from the ADR09 field and entered
in the NOUMERO field. So if the ADR09 field has for example Adams 44 entered
I need the word "Adams" to remain there and the number "44" to be be cut off
and moved to the NOUMERO field.

But note that there may be numbers in other entried at other positions I
just need the ones that are in the END to be cut off and moved. Also not all
entries have numbers. The ones that do have numbers at the end, may have 1,2
,3 or any number of digits at the end. Need all those digits to be moved. So
I guess we need to find a way to see which entries have a number after the
last space and cut and move those numbers.

Can someone please help?

Thanks
Dimitris

 

[Dale_Fye via AccessMonster.com]

You're right.

When I made the observation about the need to deconflict where instrrev(
[ADR09], " ") = 0, I was still looking at your code with the -1, so I knew
that mid([ADR09], -1) was going to generate an error. Then, when I realized
you meant to use a "+", I didn't go back and reassess the need to test for
the case where there were no spaces.

A better test would have been
IsNumeric(Mid(Adr09,InstrRev(Adr09 & ""," ")+1))

There is really no need to test for the presence of the space unless an
address would consist of ONLY numbers, but I did need to add a zero-length
string to handle null values in ADR09 and avoid an error when calling InstrRev.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John made the assumption (based on your one example) that all the records
which have a space and where the space is followed by a numeric value should

[quoted text clipped - 34 lines]

 

[John Spencer]

That's OK. I did have an error and I am glad someone caught it so the poster
did not suffer from my mistake.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

You're right.

When I made the observation about the need to deconflict where instrrev(
[ADR09], " ") = 0, I was still looking at your code with the -1, so I knew
that mid([ADR09], -1) was going to generate an error. Then, when I realized
you meant to use a "+", I didn't go back and reassess the need to test for
the case where there were no spaces.

A better test would have been
IsNumeric(Mid(Adr09,InstrRev(Adr09 & ""," ")+1))

There is really no need to test for the presence of the space unless an
address would consist of ONLY numbers, but I did need to add a zero-length
string to handle null values in ADR09 and avoid an error when calling InstrRev.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John made the assumption (based on your one example) that all the records
which have a space and where the space is followed by a numeric value should

[quoted text clipped - 34 lines]

Thanks
Dimitris