selecting string from delimited field 1

[bravo6]

I know how to use the Left, Right and Mid functions to select parts of a field by their position in the field eg. mid([field],2,3)

Is there a way to do the same thing if the field contains a delimiter? For example, if my field value is 23.639.rf2 how can I have the system return everything to the left of the period or everything between the periods? The length of each "section" of the string can differ so I need to use the delimiter and not a constant position number.

Thanks

 

[kjv1611]

Using an array string variable and the Split() function.

http://visualbasic.about.com/od/learnvb6/l/blvbsplit.htm

 

[bravo6]

No way to do this without VBA? I not VBA literate.

 

[genomon]

I know how to use the Left, Right and Mid functions...

I not VBA literate.

Sure you are, and you proved it by learning those 3 functions. Have a look at the Topics in Access help, and the link shown above. Stop selling yourself short - it's easier than it sounds!

Ni neart go cur le cheile.

 

[dhookom]

bravo6,
Create a new, blank module and copy this code into it:

Public Function Parse(varText As Variant, _
        intSectionNumber As Integer, _
        Optional strDelimiter As String = ",") As Variant
    Dim arParse As Variant
    If Not IsNull(varText) Then
        arParse = Split(varText, strDelimiter)
        If UBound(arParse) >= intSectionNumber - 1 Then
            Parse = arParse(intSectionNumber - 1)
        End If
    End If
End Function

Save the module with the name "modSpecialFunctions".

You can then in a query, you can display parts of the field value with expressions like:

   FirstColumnName: Parse([FieldName],1,".")
   SecondColumnName: Parse([FieldName],2,".")
   ThirdColumnName: Parse([FieldName],3,".")

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

dhookom, you're a genius!

All the time I've been doing the Access VBA stuff, I had wondered about combining queries and VBA, but always learned and did it by running SQL commands. There were times that I would have liked to just do what you're showing here - use VBA functions inside of Access Queries.

I'm all giddy now with excitement!


I'll have to try it out if for no other reason to just try it out! Thanks for posting that.

bravo6,

Be sure to post back, letting us know how things go. If you run into any road blocks, let us know that, as well as if you get it working - tell us what worked for you.

 

[dhookom]

kjv1611,
You can use any of your user-defined functions almost anywhere you would use any other function. One exception is default field values in table definitions. There is no difference between using Parse() or Mid() in a query.

I prefer to not use the term "giddy". Please use "geek rush" or "progasm" in the future ;-).


Duane
Hook'D on Access
MS Access MVP