Seperation of Numbers and Text from alphanumeric string

[rramkrishna]

Hi,

Can any one hlp me MS-Access VBA code to seperate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

Please help, I am using MS Access 2007

Thanks
Ram

 

[recyan]

Have you checked out VAL() ?

Thanks

 

[rramkrishna]

Yes I had checked, this function doesn't thre at MS-Access.

 

[pr2-eugin]

What do you mean the Function doesn't there?? Val is a valid function..

? Val("5789065432abcdefghijklT")
 5789065432 
? Val("598790abcdef2T")
 598790

 

[rramkrishna]

I am sorry, the function is available, however when I used the same in a query the result is showing as 0

 

[pr2-eugin]

Can you show the Query you have used?

 

[rramkrishna]

its a update query to col1 data to col2

val([tabl1]!col1)

all my data in the above format in column 1 I would like to update in column 2 1st part of numbers

 

[rramkrishna]

sorry query is val([tabl1]![col1])

 

[CJ_London]

Have you tried

val([tabl1]![col1])

 

[pr2-eugin]

This should be your UPDATE Query..

UPDATE tableName SET tableName.secondColumn = Val(tableName.firstColumn);

 

[CJ_London]

posted over!

Can you confirm your data is as expected - i.e. col1 starts with numeric characters

Also perhaps post the whole query SQL, there may be an issue elsewhere

 

[pr2-eugin]

Also perhaps post the whole query SQL, there may be an issue elsewhere

That's what I expected to see, when I asked for the Query..

 

[rramkrishna]

Thanks Paul, the query worked and got the output desired.

Thanks again for your help.

 

[pr2-eugin]

Glad you have it sorted.. Good Luck !!