Moving the last numbers of a field

[Dimitris]

Hello in table ADDRDATA we have the field ODOS and the field NOUMERO. The
field ODOS has many entries that end with numbers. I need those numbers
erased from the ODOS field and entered in the NOUMERO field. So if the ODOS
field has for example Smith 55 entered, I want the "Smith" to remain there
and the "55" to be cut off and moved to the NOUMERO field.

But note that there may be numbers in other entries at other positions. I
just want the ones that are in the END to be cut and moved. Also not all
entries do 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 last digits to be
moved.

Can someone help me please?

Thank you
Dimitris

 

[Clifford Bass]

Hi Dimitris,

You will want to use the InStrRev() to find the last space and the
InNumeric() function to determine if the last part is numeric. Try the
following in a test copy of the database before doing it for real:

UPDATE ADDRDATA SET ADDRDATA.NOUMERO =
Right([ODOS],Len([ODOS])-InStrRev([ODOS]," ")), ADDRDATA.ODOS =
Left([ODOS],InStrRev([ODOS]," ")-1)
WHERE (((ADDRDATA.NOUMERO) Is Null) AND
((IsNumeric(Right([ODOS],Len([ODOS])-InStrRev([ODOS]," "))))=True));

Clifford Bass

 

[Dimitris]

It worked just great.
Thank you for your help Clifford.

Hi Dimitris,

You will want to use the InStrRev() to find the last space and the
InNumeric() function to determine if the last part is numeric. Try the
following in a test copy of the database before doing it for real:

UPDATE ADDRDATA SET ADDRDATA.NOUMERO =
Right([ODOS],Len([ODOS])-InStrRev([ODOS]," ")), ADDRDATA.ODOS =
Left([ODOS],InStrRev([ODOS]," ")-1)
WHERE (((ADDRDATA.NOUMERO) Is Null) AND
((IsNumeric(Right([ODOS],Len([ODOS])-InStrRev([ODOS]," "))))=True));

Clifford Bass

Hello in table ADDRDATA we have the field ODOS and the field NOUMERO. The
field ODOS has many entries that end with numbers. I need those numbers
erased from the ODOS field and entered in the NOUMERO field. So if the ODOS
field has for example Smith 55 entered, I want the "Smith" to remain there
and the "55" to be cut off and moved to the NOUMERO field.

But note that there may be numbers in other entries at other positions. I
just want the ones that are in the END to be cut and moved. Also not all
entries do 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 last digits to be
moved.

Can someone help me please?

Thank you
Dimitris

 

[Clifford Bass]

Hi Dimitris,

Very good. You are welcome.

Clifford Bass