[AccessD] Split Field

You could use the Trim function to get rid of the spaces once you have the
position of the last dash in the string (code follows)

Sub GetRefNum()
    Dim X As Integer
    Dim intPos As Integer
    Dim strRefNum As String
    Dim YourString As String
    
    YourString = "ADTS - Outpatient Program Admission And Discharge Criteria
- PPM No. 51.08"
    intPos = 0
    'Find the last occurrence of the -
    X = InStr(1, YourString, "-")
    Do While X > 0
        intPos = X
        X = InStr(X + 1, YourString, "-")
    Loop
    
    If intPos = 0 Then
        strRefNum = YourString
    Else
        strRefNum = Trim(Right(YourString, Len(YourString) - intPos))
    End If

    MsgBox strRefNum
    
End Sub

HTH

Rusty

-----Original Message-----
From: Dwylene Garcia [mailto:garciad at kentri.org]
Sent: Tuesday, July 08, 2003 9:10 AM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Split Field


I have the following type of information in one field:

ADTS - Outpatient Program Admission And Discharge Criteria - PPM No.
51.08
Human Resources Plan -- A15.04
Medical Staff Rules & Regulations - A10.03
Admission Criteria for Inpatient Hospitalization - PPM No. 25.07

I would like to split it so the reference number at the end of the
field is in a separate field.   The problem is, the number of spaces is
not consistent.  Can I work off the "-" somehow?



Dwylene Garcia

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com