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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Quote:
> 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