Hi,

I'm using Access 97.

I want to write a function which takes in a string (eg,
USA 123456789) and it can extract the numeral part(ie
123456789) as string. There's a space between the text and
the numeral part.

i'm thinking to get the index of the space, and use
right(textfield, textfielf's length - index). But i'm not
sure if they have get length and index function for string
in Access 97. I just don't seem to find it help.

Can you pls provide some help?

Thanks in advance!

Regards,
Irene

Irene

You will want to use the Split function. Take a look at this article below.
I also created an example you can use to see how it works.

How do I simulate the VB Split function in VBA?
http://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/27/pid/705/fid/1823

Let me know if you need any other help.

Rob

Public Sub Example()

  Dim myString As String
  Dim myArray() As String
  Dim x As Integer

  myString = "ABC 123 XYZ"
  myArray() = Split(myString)

  For x = LBound(myArray) To UBound(myArray)
    MsgBox Prompt:=myArray(x), Title:="Breaking string into pieces"
  Next x

End Sub

--
Rob

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-Having trouble with your database?
-Are you ready to upgrade, but don't have the expertise?
Let the FMS Professional Solutions Group solve it for you.
www.FMSInc.com/Consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

They sure do - but they aren't named that. Look at online help for
Left(), Right(), Mid() and InStr(). To extract the portion of the
field named MyField after the first blank in the field use an
expression

Mid(MyField, InStr([MyField], " ") + 1)

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Irene,

<< I want to write a function which takes in a string (eg,
USA 123456789) and it can extract the numeral part(ie
123456789) as string. There's a space between the text and
the numeral part. >>

Use the VAL function.  The value of VAL(USA 123456789)
will be just the numeric part.

Greg

On Tue, 22 Apr 2003 06:08:59 -0700, "Gregory Serrano"

ummm... sorry, no, it won't. Val("USA 123456789") is 0; it only takes
the substring up to the first nonnumeric character.

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public