'Right' Function

[deiopajw]

I have a field called Product.
There are items such as:
AB1000
AB400
AB1100

I want to extract only the numbers in the product.
I tried to enter the following in the source control of a field I created
to simply show the number section of the product:
=Right([Product],4)
However, product AB400 would show up as B400.
Can anyone suggest how to overcome this problem.

 

[Guest]

Hi

Use the following code:

Mid([product],3,20)

where 3 is the first letter of the string that you want to extract
and 20 is the number of characters thereafter (if there are only four
characters after then the remaining 16 are ignored.

Kind regards

Colin

 

[Guest]

If the left part is always 2 characters and it is the numeric part that
varies then use the mid function instead of the right function
=Mid([Product],3)

 

[Brendan Reynolds]

If it is always the first two characters that are non-numeric, then
Mid$([Product], 3) will get you the numeric portion.

If the number of non-numeric characters varies, you'll need a custom VBA
function.

 

[Douglas J Steele]

Actually, the 3rd parameter is optional in the Mid function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

Use the following code:

Mid([product],3,20)

where 3 is the first letter of the string that you want to extract
and 20 is the number of characters thereafter (if there are only four
characters after then the remaining 16 are ignored.

Kind regards

Colin

I have a field called Product.
There are items such as:
AB1000
AB400
AB1100

I want to extract only the numbers in the product.
I tried to enter the following in the source control of a field I created
to simply show the number section of the product:
=Right([Product],4)
However, product AB400 would show up as B400.
Can anyone suggest how to overcome this problem.