String manipulation 
Author Message
 String manipulation

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



Sat, 08 Oct 2005 10:11:45 GMT  
 String manipulation
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



Sat, 08 Oct 2005 10:50:53 GMT  
 String manipulation

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.

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



Sat, 08 Oct 2005 13:23:37 GMT  
 String manipulation
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



Sat, 08 Oct 2005 21:08:59 GMT  
 String manipulation
On Tue, 22 Apr 2003 06:08:59 -0700, "Gregory Serrano"

Quote:

>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.

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



Sun, 09 Oct 2005 01:27:17 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. string manipulation, string routines, etc.

2. String Manipulation

3. VBA String Manipulation DLL Missing

4. String manipulation in VBA

5. String Manipulation

6. String manipulation with vbscript

7. String manipulation topic

8. String manipulation...

9. Text String Manipulation

10. Newbie question : string manipulation with VBscript.

11. String manipulation

12. String manipulation

 

 
Powered by phpBB® Forum Software