split field data

[Alex]

Hi
The right hand data of Field1 is a numeric number consisting of either 3, 4
or 5 characters. I need to update field3 with this information

Is there an easy way to do this please

thanks
A

 

[kingston via AccessMonster.com]

Since I cannot assume that the fields are consistent in any other manner, I
can only provide you a three step solution:

Make an update query to write to field 3. Make the criteria Is Null (I
assume that Field 3 starts out as Null) and make the update this:

IIF(IsNumeric(Right(Field1,5)),Right(Field1,5),Null)

Run the query and change the update to:

IIF(IsNumeric(Right(Field1,4)),Right(Field1,4),Null)

Run the query and change the update to:

IIF(IsNumeric(Right(Field1,3)),Right(Field1,3),Null)

 

[John Nurick]

Hi Alex,

One way is to use the function at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm in an
update query.

Updating Field3 to
RgxExtract([Field1], "\d{3,5}$")
should do the trick.

 

[Alex]

Thanks Both

John -That is brilliant i can use that so many times - made my day

Alex