Extracting part of a text value

[Pete]

I have a text value that ranges from 5 to 9 characters. The last two
characters are always two letters. All of the other characters are always
numbers. I want to separate the letters from the numbers - keeping the
numbers. I do not need the letters.

The "Left", "Right", and "Mid" expressions don't get me what I need.

How can I extract just the numbers?

Thanks in advance!

 

[raskew via AccessMonster.com]

Hi -

The Val() function will give you what you describe. Example:

x = "12345JT"
? val(x)
12345

HTH - Bob

 

[John Spencer]

As long as the field is always at least 3 characters in length the following
should work.

Left(YourField, Len(YourField)-2)

If you wish you can test
IIF(Len([YourField] & "")> 3, Left([YourField], Len([YourField])-2), Null)

IF you want to return whatever is in your your field if it has 2 or fewer
characters than replace null with [YourField]


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County