Prev: Summarize dates for like records
Next: Access 2003 Currency Formatting
From: Pete on 23 Jun 2008 11:58
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!


From: raskew via AccessMonster.com on 23 Jun 2008 12:16
Hi -

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

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

HTH - Bob

Pete wrote:
>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!

--
Message posted via http://www.accessmonster.com

From: John Spencer on 23 Jun 2008 13:38
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

Pete wrote:
> 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!
>
>
 | 
Pages: 1
Prev: Summarize dates for like records
Next: Access 2003 Currency Formatting